Some VBA utility functions / subs for Excel and Access.
VBA is a fairly old application and not up to date with modern IDE standards. There are some ways to adapt VBA by changing some setting or even adding add-ins.
The following formatting and tools were suggested from GollyJer's Stackoverflow post
Each time you jump to a new line with syntax errors in you existing line of code, you will get a pop-up. To deactivte the pop-up (errors will still get highlighted) in the Visual Basics Editor do the following:
Tools -> Options -> Editor Tab -> deselect Auto Syntax Check
For a more readable / modern Font that is included in the standard Windows Font in the Visual Basics Editor do the following:
Tools -> Options -> Format Tab -> change the Font to Consolas (Western)
and the size to 11
For easier commenting / uncommenting of code to make the Edit toolbar accessible in the taskbar in Visual Basics Editor do the following:
Right Click the toolbar / menu -> Select the Edit
toolbar, so it will appear as an additional toolbar which can be drag & dropped at convenience
Ruberduck is an add-in that adds a wide set of feature to VBA (such as auto-indenting). According to their webpage they:
Bring the VBE into this century Or help us do it! Rubberduck is a very active open-source COM add-in project that integrates with the Visual Basic Editor to enable the features every programmer wants to have in their IDE. From unit testing to code inspections and refactorings, programming in VBA will never be the same.
Download and install the rubberduck extension
Website: http://rubberduckvba.com/
Github: https://github.com/rubberduck-vba/Rubberduck/releases
Paul Kelly from excelmacromastery.com gives a great overview on variable types and their initilization.
Variable Type | Abbbreviation | Example | Description |
---|---|---|---|
String |
str | Dim str_name as String |
Basic |
Long |
lng | Dim lng_counter as Long |
Basic |
Double |
dbl | Dim dbl_variance as Double |
Basic |
Currency |
ccy | Dim ccy_cost as Currency |
Basic |
Boolean |
bln | Dim bln_status as Boolean |
Basic |
Variant |
var | Dim var_list as Variant |
Variant / Array |
There is no reason to use Integer
over Long
due to it's limitations, as described by RubberDuck on Stackoverflow.