Useful libraries for Excel VBA:
-
LibExcelTables.bas
UsefulListObject
related methods:AddListRows
,DeleteListRows
: adds/deletes a variable number ofListRows
to/from aListObject
and works even if the parentWorksheet
is protected with theUserInterfaceOnly
flag set toTrue
without the need to unprotect. See related SO answerGetListObject
: retrieve table by name without the need to know the parentWorksheet
IsListObjectFiltered
: check if table is filtered without the need for error handlingSortListObjectIfNeeded
: sort a table only if needed. When sort is not needed, it is faster to iterate through values and not call the built-in sort method
-
LibExcelBookItems.bas
Store/retrieveString
items in aWorkbook
using encapsulated custom XML functionality. No need to write any XML.BookItem
: parametric property Get/Let. To delete a property simply set the value to a null string e.g. BookItem(ThisWorkbook, "itemName") = vbNullStringGetBookItemNames
: retrieve a collection of all item names
-
ExcelTable.cls
Easy storage of tabular data in Excel within a single class. Requires theLibMemory
submodule - see the Submodules section belowCan be initialized via:
InitFromListObject
: 1 row headers always non-blank and uniqueInitFromRange
: joins multi header rows and makes them unique using the same strategy as a ListObject
Can return the following arrays:
ColumnFormats
: a copy of the internal formats arrayDataByVal
: a copy of the internal data arrayDataByRef
: the data array wrapped inside a ByRef Variant to avoid copy - array cannot be resized because it's made 'static' at Init but values can be updated/erasedHeadersArray
: a copy of the internal headers array
Has the following utilities:
ColumnCount
: returns the number of headers/columnsHeaderAtIndex
: returns the header string at a given indexHeaderExists
: checks if a header string existsIndexForHeader
: returns the index for a header stringRowCount
: returns the number of data rowsSelf
: returns the instance
-
ExcelAppState.cls
This class is useful to store/modify/restore application settings in order to speed up code execution when interacting with certain parts of the application. The first section of this CR answer explains why this class/approach is needed.Has the following utilities:
StoreState
: stores the current state for a couple of application settingsRestoreState
: restores the state as it was saved via theStoreState
methodSleep
: turns a few application settings off but has the ability to keep some/all of them on via the optional parametersWake
: turns a few application settings on but has the ability to keep some/all of them off via the optional parametersWaitForCalculations
: waits for theApplication.CalculationState
to be equal toxlDone
and even fixes a bug that occurs when volatile formulas are present. ThemaxMilliSecondsToWait
parameter allows the user to define a timeout period in case the calculation takes too long/is stuckClearStateIfNeeded
clears the saved state (if any) so thatRestoreState
is not called on instance termination. Rarely needed
-
BookConnection.cls
This class is useful for opening workbooks or just accessing them if they are already opened. The open state is stored and books that were opened are closed whenDisconnect
is called.Has the following utilities:
Connect
: opens a workbook by full path or retrieves a book that is already opened. The open state is savedDisconnect
: closes a book that was opened withConnect
or does nothing if the book was already open whenConnect
was calledSelf
: returns the instanceWorkbook
: returns the workbook object that was specified whenConnect
was called
Some of the modules in this repository require some additional library code modules which are available in the submodules folder or you can get their latest version here:
Note that submodules are not available in the Zip download. If cloning via GitHub Desktop the submodules will be pulled automatically by default. If cloning via Git Bash then use something like:
$ git clone https://github.com/cristianbuse/Excel-VBA-Tools
$ git submodule init
$ git submodule update
or:
$ git clone --recurse-submodules https://github.com/cristianbuse/Excel-VBA-Tools
MIT License
Copyright (c) 2022 Ion Cristian Buse
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.