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
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.
A standard comment starts with
' This is a comment
but you can also use the REM statement to create comments:
REM ***** Hello World *****
The major entry point is the subroutine Main
Sub Main REM Magic happens here End Sub
Debug.Print "Starting Program"
Variables are declared the following way
Dim totalPrice as Long
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 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
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)