Skip to content

Some VBA utility functions / subs for Excel and Access.

License

Notifications You must be signed in to change notification settings

sebinum/vba_utility

Folders and files

NameName
Last commit message
Last commit date

Latest commit

1df89e5 · Jan 20, 2020

History

35 Commits
Apr 25, 2018
Sep 9, 2018
Mar 13, 2018
Jan 20, 2020

Repository files navigation

vba_utility

Some VBA utility functions / subs for Excel and Access.

Setting up Visual Basic for Applications (VBA)

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.

Formatting & Tools

The following formatting and tools were suggested from GollyJer's Stackoverflow post

1 Deactivate Auto Syntax Check

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

2 Change Font

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

3 Make Edit Toolbar accessible

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

4 Add-ins - Ruberduck

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

Coding Style Guideline applied in this project

Paul Kelly from excelmacromastery.com gives a great overview on variable types and their initilization.

Types and their declaration

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.

About

Some VBA utility functions / subs for Excel and Access.

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages