stdVBA

A Collection of libraries to form a common standard layer for modern VBA applications.

Benefits

Code faster!
Improve code maintainability.
Let the library handle the complicated stuff, you focus on the process
Heavily inspired by JavaScript APIs - More standard
Open Source - Means the libraries are continually maintained by the community. Want something added, help us make it!

The full roadmap has more detailed information than here.

Getting started

A video series is being created to demonstrate how to get started with stdVBA. Alternatively there is a small getting started document in the discussions

Short example

Please visit the examples repository for more fully featured applications/examples using this library.

sub Main()
  'Create an array
  Dim arr as stdArray
  set arr = stdArray.Create(1,2,3,4,5,6,7,8,9,10) 'Can also call CreateFromArray

  'Demonstrating join, join will be used in most of the below functions
  Debug.Print arr.join()                                                 '1,2,3,4,5,6,7,8,9,10
  Debug.Print arr.join("|")                                              '1|2|3|4|5|6|7|8|9|10

  'Basic operations
  arr.push 3
  Debug.Print arr.join()                                                 '1,2,3,4,5,6,7,8,9,10,3
  Debug.Print arr.pop()                                                  '3
  Debug.Print arr.join()                                                 '1,2,3,4,5,6,7,8,9,10
  Debug.Print arr.concat(stdArray.Create(11,12,13)).join                 '1,2,3,4,5,6,7,8,9,10,11,12,13
  Debug.Print arr.join()                                                 '1,2,3,4,5,6,7,8,9,10 'concat doesn't mutate object
  Debug.Print arr.includes(3)                                            'True
  Debug.Print arr.includes(34)                                           'False

  'More advanced behaviour when including callbacks! And VBA Lamdas!!
  Debug.Print arr.Map(stdLambda.Create("$1+1")).join          '2,3,4,5,6,7,8,9,10,11
  Debug.Print arr.Reduce(stdLambda.Create("$1+$2"))           '55 ' I.E. Calculate the sum
  Debug.Print arr.Reduce(stdLambda.Create("application.worksheetFunction.Max($1,$2)"))      '10 ' I.E. Calculate the maximum
  Debug.Print arr.Filter(stdLambda.Create("$1>=5")).join      '5,6,7,8,9,10
  
  'Execute property accessors with Lambda syntax
  Debug.Print arr.Map(stdLambda.Create("ThisWorkbook.Sheets($1)")) _ 
                 .Map(stdLambda.Create("$1.Name")).join(",")            'Sheet1,Sheet2,Sheet3,...,Sheet10
  
  'Execute methods with lambdas and enumerate over enumeratable collections:
  Call stdEnumerator.Create(Application.Workbooks).forEach(stdLambda.Create("$1.Save")
  
  'We even have if statement!
  With stdLambda.Create("if $1 then ""lisa"" else ""bart""")
    Debug.Print .Run(true)                                              'lisa
    Debug.Print .Run(false)                                             'bart
  End With
  
  'Execute custom functions
  Debug.Print arr.Map(stdCallback.CreateFromModule("ModuleMain","CalcArea")).join  '3.14159,12.56636,28.274309999999996,50.26544,78.53975,113.09723999999999,153.93791,201.06176,254.46879,314.159

  'Let's move onto regex:
  Dim oRegex as stdRegex
  set oRegex = stdRegex.Create("(?<county>[A-Z])-(?<city>\d+)-(?<street>\d+)","i")

  Dim oRegResult as object
  set oRegResult = oRegex.Match("D-040-1425")
  Debug.Print oRegResult("county") 'D
  Debug.Print oRegResult("city")   '040
  
  'And getting all the matches....
  Dim sHaystack as string: sHaystack = "D-040-1425;D-029-0055;A-100-1351"
  Debug.Print stdEnumerator.CreateFromIEnumVARIANT(oRegex.MatchAll(sHaystack)).map(stdLambda.Create("$1.item(""county"")")).join 'D,D,A
  
  'Dump regex matches to range:
  '   D,040,040-1425
  '   D,029,029-0055
  '   A,100,100-1351
  Range("A3:C6").value = oRegex.ListArr(sHaystack, Array("$county","$city","$city-$street"))
  
  'Copy some data to the clipboard:
  Range("A1").value = "Hello there"
  Range("A1").copy
  Debug.Print stdClipboard.Text 'Hello there
  stdClipboard.Text = "Hello world"
  Debug.Print stdClipboard.Text 'Hello world

  'Copy files to the clipboard.
  Dim files as collection
  set files = new collection
  files.add "C:\File1.txt"
  files.add "C:\File2.txt"
  set stdClipboard.files = files

  'Save a chart as a file
  Sheets("Sheet1").ChartObjects(1).copy
  Call stdClipboard.Picture.saveAsFile("C:\test.bmp",false,null) 'Use IPicture interface to save to disk as image
End Sub

Public Function CalcArea(ByVal radius as Double) as Double
  CalcArea = 3.14159*radius*radius
End Function

Motivation

VBA first appeared in 1993 (over 25 years ago) and the language's age is definitely noticable. VBA has a lot of specific libraries for controlling Word, Excel, Powerpoint etc. However the language massively lacks in generic modern libraries for accomplishing common programming tasks. VBA projects ultimately become a mish mash of many different technologies and programming styles. Commonly for me that means calls to Win32 DLLs, COM libraries via late-binding, calls to command line applications and calls to .NET assemblies.

Over time I have been building my own libraries and have gradually built my own layer above the simple VBA APIs.

The VBA Standard Library aims to give users a set of common libraries, maintained by the community, which aid in the building of VBA Applications.

Road Map

This project is has been majorly maintained by 1 person, so progress is generally very slow. This said, generally the road map corresponds with what I need at the time, or what irritates me. In general this means fundamental features are more likely to be complete first, more complex features will be integrated towards the end. This is not a rule, i.e. stdSharepoint is mostly complete without implementation of stdXML which it'd use. But as a general rule of thumb things will be implemented in the following order:

Types - stdArray, stdDictionary, stdRegex, stdDate, stdLambda, ...
Data - stdJSON, stdXML, stdOXML, stdCSON, stdIni, stdZip
File - stdShell
Automation - stdHTTP, stdAcc, stdWindow, stdKernel
Excel specific - xlFileWatcher, xlProjectBuilder, xlTimer, xlShapeEvents, xlTable
Runtimes - stdCLR, stdPowershell, stdJavascript, stdOfficeJSBridge

As an indicator of where my focuses have been in the past year, take a look at the following heat map:

changesHeatMap

Planned Global Objects:

|Color | Status | Type |Name |Docs | Description | |----------------------------------------------------------|--------|------------|-----------------|-----|--------------| | | HOLD | Debug |stdError | None | Better error handling, including stack trace and error handling diversion and events. | | READY | Type |stdArray | None | A library designed to re-create the Javascript dynamic array object. | | READY | Type |stdEnumerator | docs | A library designed to wrap enumerable objects providing additional functionality. | | WIP | Type |stdDictionary | None | A drop in replacement for VBScript's dictionary. | | READY | Type |stdDate | None | A standard date parsing library. No more will you have to rely on Excel's interpreter. State the format, get the data. | | READY | Type |stdRegex | None | A regex library with more features than standard e.g. named capture groups and free-spaces. | | READY | Type |stdLambda | docs | Build and create in-line functions. Execute them at a later stage. | | READY | Type |stdCallback | None | Link to existing functions defined in VBA code, call them at a later stage. | | READY | Type |stdCOM | None | A wrapper around a COM object which provides Reflection (through ITypeInfo), Interface querying, Calling interface methods (via DispID) and more. | | READY | Automation |stdClipboard | None | Clipboard management library. Set text, files, images and more to the clipboard. | | HOLD | Automation |stdHTTP | None | A wrapper around Win HTTP libraries. | | READY | Automation |stdWindow | docs | A handy wrapper around Win32 Window management APIs. | | READY | Automation |stdProcess | None | Create and manage processes. | | READY | Automation |stdAcc | docs | Use Microsoft Active Accessibility framework within VBA - Very useful for automation. | | READY | Automation |stdWebSocket | None | WebSocket automation. Currently uses IE, need to move to more stable runtime. Will be useful for modern automation e.g. chrome | | WIP | Excel |xlTable | None | Better tables for VBA, e.g. Map rows etc. | | READY | DevTools |stdPerformance | None | Performance testing

The full roadmap has more detailed information than here.

StdVBA

Install / Use

README