Repository files navigation

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

• 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.

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

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

subMain()'Create an arrayDimarrasstdArraysetarr=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 functionsDebug.Printarr.join()'1,2,3,4,5,6,7,8,9,10Debug.Printarr.join("|")'1|2|3|4|5|6|7|8|9|10'Basic operationsarr.push3Debug.Printarr.join()'1,2,3,4,5,6,7,8,9,10,3Debug.Printarr.pop()'3Debug.Printarr.join()'1,2,3,4,5,6,7,8,9,10Debug.Printarr.concat(stdArray.Create(11,12,13)).join'1,2,3,4,5,6,7,8,9,10,11,12,13Debug.Printarr.join()'1,2,3,4,5,6,7,8,9,10 'concat doesn't mutate objectDebug.Printarr.includes(3)'TrueDebug.Printarr.includes(34)'False'More advanced behaviour when including callbacks! And VBA Lamdas!!Debug.Printarr.Map(stdLambda.Create("$1+1")).join'2,3,4,5,6,7,8,9,10,11Debug.Printarr.Reduce(stdLambda.Create("$1+$2"))'55 ' I.E. Calculate the sumDebug.Printarr.Reduce(stdLambda.Create("application.worksheetFunction.Max($1,$2)"))'10 ' I.E. Calculate the maximumDebug.Printarr.Filter(stdLambda.Create("$1>=5")).join'5,6,7,8,9,10'Execute property accessors with Lambda syntaxDebug.Printarr.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:CallstdEnumerator.Create(Application.Workbooks).forEach(stdLambda.Create("$1.Save")'We even have if statement!WithstdLambda.Create("if $1 then ""lisa"" else ""bart""")Debug.Print.Run(true)'lisaDebug.Print.Run(false)'bartEndWith'Execute custom functionsDebug.Printarr.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:DimoRegexasstdRegexsetoRegex=stdRegex.Create("(?<county>[A-Z])-(?<city>\d+)-(?<street>\d+)","i")DimoRegResultasobjectsetoRegResult=oRegex.Match("D-040-1425")Debug.PrintoRegResult("county")'DDebug.PrintoRegResult("city")'040'And getting all the matches....DimsHaystackasstring:sHaystack="D-040-1425;D-029-0055;A-100-1351"Debug.PrintstdEnumerator.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-1351Range("A3:C6").value=oRegex.ListArr(sHaystack,Array("$county","$city","$city-$street"))'Copy some data to the clipboard:Range("A1").value="Hello there"Range("A1").copyDebug.PrintstdClipboard.Text'Hello therestdClipboard.Text="Hello world"Debug.PrintstdClipboard.Text'Hello world'Copy files to the clipboard.Dimfilesascollectionsetfiles=newcollectionfiles.add"C:\File1.txt"files.add"C:\File2.txt"setstdClipboard.files=files'Save a chart as a fileSheets("Sheet1").ChartObjects(1).copyCallstdClipboard.Picture.saveAsFile("C:\test.bmp",false,null)'Use IPicture interface to save to disk as imageEndSubPublicFunctionCalcArea(ByValradiusasDouble)asDoubleCalcArea=3.14159*radius*radiusEndFunction

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.

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 meansfundamental 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 ofstdXML 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:

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.

APIs which are ready to use, and although are not fully featured are in a good enough working state.

APIs which are WIP are not necessarily being worked on currently but at least are recognised for their importance to the library. These will be lightly worked on/thought about continuously even if no commits are made.

As of Oct 2020, this status typically consists of:

• data types, e.g. stdEnumerator, stdDictionary, stdTable;
• Unit testing;
• Tasks difficult to automate otherwise e.g. stdClipboard, stdAccessibility;

APIs where progress has been temporarily halted, and/or is currently not a priority.

In the early days we'll see this more with things which do already have existing work arounds and are not critical, so projects are more likely to fit into this category.

APIs which have been indefinitely halted. We aren't sure whether we need these or if they really fit into the project. They are nice to haves but not necessities for the project as current. These ideas may be picked up later. All feature requests will fit into this category initially.

All modules or classes will be prefixed bystd if they are generic libraries.

Application specific libraries to be prefixed withxl,wd,pp,ax representing their specific application.

Commonly implementations will use the factory class design pattern:

ClassstdSomeClassPrivatebInitialisedasboolean'Creates an object from the given parameters'@constructorPublicFunctionCreate(...)AsstdSomeClassifnotbInitialisedthenSetCreate=NewstdSomeClassCallCreate.protInit(...)elseCallCriticalRaise("Constructor called on object not class")EndIfEndFunction'Initialises the class. This method is meant for internal use only. Use at your own risk.'@protectedPublicSubprotInit(...)IfbInitialisedThenCallCriticalRaise("Cannot run protInit() on initialised object")elseifMeisstdSomeClassthenCallCriticalRaise("Cannot run protInit() on static class")else'initialise with params...'Make sure bInitialised is setbInitialised=trueEndIfEndSubPrivateSubCriticalRaise(ByValsMsgasstring)ifisObject(stdError)thenstdError.RaisesMsgelseErr.Raise1,"stdSomeClass",sMsgendifEndSub'...EndClass

With the above example, the Regex class is constructed with theCreate() method, which can only be called on thestdRegex static class. We will try to keep this structure across all STD VBA classes.

If you are looking to contribute to the VBA standard library codebase, the best place to start is theGitHub "issues" tab. This is also a great place for filing bug reports and making suggestions for ways in which we can improve the code and documentation. A list of options of different ways to contribute are listed below:

• If you have a Feature Request - Create a new issue
• If you have found a bug - Create a new issue
• If you have written some code which you want to contribute - See the Contributing Code section below.

There are several ways to contribute code to the project:

• Opening pull requests is the easiest way to get code intergrated with the standard library.
• Create a new issue and providing the code in a code block - Bare in mind, it will take us a lot longer to pick this up than a standard pull request.

Please make sure code contributions follow the following guidelines:

• stdMyClass.cls should haveAttribute VB_PredeclaredId = True.
• Attribute VB_Name should follow the STD convention e.g."stdMyClass"
• Follow the STD constructor conventionstdMyClass.Create(...).
• Ensure there are plenty of comments where required.
• Ensure lines end in\r\n and not\n only. You can enable the Git filter in charge of making this automatic conversion by runninggit config include.path ../.gitconfig

As long as these standard conventions are met, the rest is up to you! Just try to be as general as possible! We're not necessarily looking for optimised code, at the moment we're just looking for code that works!

Note: Ensure that all code is written by you. If the code is not written by you, you will be responsible for any repercussions!

Over the process of building this library we have drawn from numerous examples and knowledge from across the web. Many of these examples can either be found in ourinspiration library orawesome-vba.