A horrifying amount of business is still driven by Microsoft Excel. Need some stats? Excel. Need some number crunching? Excel
Most of the time you can work with formulas in your sheet and you will be fine. But sometimes you need more power:
Time to level up your VBA skills
Table of Contents
History
VBA stands for “Visual Basic for Applications” and was introduced into the Microsoft Office Products in 1993. Until the introduction of Visual Basic.NET it was a compatible dialect of the general Visual Basic language.
Comments
A standard comment starts with
' This is a comment
but you can also use the REM statement to create comments:
REM ***** Hello World *****
Modules
The major entry point is the subroutine Main
Sub Main REM Magic happens here End Sub
Debug output
Debug.Print "Starting Program"
Variables
Variables are declared the following way
Dim totalPrice as Long
Option Explicit
A good practice is to start your macro with the option explicit expression to enforce that variables are explicitly declared.
This prevents bugs by typos.
Loops
Loops can be done with the For keyword and are straightforward
Dim i as Integer
For i = 0 To 100 Step 1
Debug.Print i
Next
Conditionals
If priceA = 0 Then
temp = priceB
ElseIf priceB = 0 Then
temp = priceA
ElseIf priceA < priceB Then
temp = priceA
Else
temp = priceB
EndIf
Accessing Sheets and Cells
Dim currentDoc as Object Dim currentSheet as Object Dim currentCell as Object currentDoc = ThisComponent currentSheet = currentDoc.sheets(0) currentCell = currentSheet.getCellByPosition(0, 0)
