VBA (Visual Basic for Applications)
Learning 1: Subroutine vs Function
Subroutine can be used to write values to cells, perform calculations, but cannot use it in a formula in excel.
Shortcut to go to VBA editor: Alt+F11 → insert →module
Sub xx()
……..type your code here…….
End Sub
Function to calculate commission
To make Subroutine only limited to one workbook, type “Private Sub”
To create function only for specific sheet:
- you cannot create a function within a sheet level code module (meaning you double click on the “sheet 1” and type your code there)
- can only add function to the general code module. (not the respective sheets)
To input comment line, use ‘
Learning 2: Macro
You can assign macro action to a shape:
To assign macro to quick access tool bar, go to file →options →quick access toolbar →choose macro →Add. See below:
You can also add macros control to your ribbon panel:
You can rename the group. Then add “Display Tax” to the group:
Add code to a recorded macro
- to unblock macro, right click on the file →properties →tick unblock
Lets say you want to recreate a pivot table
Step 1 to create macro:
Select any cell in the pivot table, press PivotTable Analyze, “Actions”, clear
Step 2: “view” tab→Macros →Record Macro
Step 3: put the relevant field you want to create the pivot table.
Step 4: stop macro recording
Learning 3
VBA Data types:
E38 means 10³⁸
Declare variables and require declaration before use:
Dim — dimension
To make sure variable is defined correctly, you can type “Option Explicit” →requires programmer to declare variable before using them
If don't type “Option Explicit” and variable name is used wrongly, results will show zero as variable is not assigned to anything.
If type “Option Explicit” and variable name is used wrongly, module code will immediately prompt an error.
Declare more than one variable with same data type:
Setting global variable so that they can be shared among all the subroutines and functions within a code module.
To run the respective subroutine, make sure your cursor is in the right place:
If you are going to use a variable in more than one subroutine within a code module, you can make it a global variable.
Step 1: cursor above “Sub” line would bring you to declaration
since you already declare “curRate as currency” as a global variable, you don't need to do it inside of the subroutines, so can just comment out/delete the “Dim curRate as currency” lines within each subroutine.
- You cannot assign a value to a global variable when you declare it. e.g. curRate=0.05
- Declare global variable help you save time not repeating in each subroutine. But you may forget the variable name, thus use global variable with “option explicit”. → Doing so requires anyone who uses a variable within a subroutine to declare it either as a global variable or as a local variable.
Define constant and static variable → so that can be used for every new subroutines
Const → constant variable never change
Static variable → allows it to change as it goes along. but it only retain their accumulated value while the workbook is open. Once you close it, the variable is set back to zero.
Round ( ,2) → 2 d.p.
\ →find the ratio
Arrays →dealing with set of values of the same type, such as a set of shipping rates
curShippingCharges(5) → (5) means there are 6 slots in the array, 0,1,2,3,4,5
we can’t assign values directly to array.
Define and use object variables:
“Set” used for object variable
vbRed →visual basic Red
With End blocks
To increase your programming efficiency
For…Next loop
way to repeat code in Excel VBA
To reverse can type: 5 to 0
For…Each loop
If you want your code to affect every member of a collection or an array, you can use a for each loop to interact with every element in the array or the collection.
Chpt 3 — Do loop
you expect it to perform a specific task until a condition is met.
Step through every step of code by pressing F8
<> → not blank
To stop the loop: press “Esc” or Ctrl+c
Scenario: identify the first empty cell in column A
If…Then
IIF vba = If() in excel
Case
A case statement is more compact than if-then statements
Case statements are a great way to identify outcomes based on a series of criteria.
Chpt 4 — On Error
On Error GoTo 0:
is actually the default error handling method for Excel VBA. In general, you would never use On Error GoTo 0 unless you were trying to set from another error handling mode back to the default.
On Error GoTo Handler:
on an error, regardless of where the error occurs, the next line that is executed is below this line labeled “Handler”. (this is to anticipate any user mishandling of excel)
Exit sub tells Excel VBA to stop running in a code and to end executing the subroutine.
End sub indicates the end of all the code, and it also causes Excel to exit the subroutine and stop executing.
On Error Resume Next:
if there was a problem, then there’s no indication from the code that something happened.
Step through a subroutine or function
can hover over the code to see the value that has been assigned to it
two other ways that you can use stepping to move through your subroutines: step over and step out
If you know your code does work properly then you can step over (skip) it by clicking shift f8.
To step out (already in, now you want to go out) press control shift f8.
Breakpoint
Excel runs the code to the break point and stops until you tell it to continue.
Stop here until otherwise. You can limit what runs and see what’s happening as you go.
Verify output using immediate window rather than message boxes
press Control + G. and the Immediate window appears at the bottom of the Visual Basic Editor program window
Can put ? then strState, to see the output. For strCity, because there is a breakpoint, excel return null value.
When you close the immediate window and reopen it, the contents will still remain. you have to delete manually.
“Watch” a variable value in a routine
create a Watch that displays a value of a variable continuously
Go back to Debug>edit watch → to delete/add/edit watch
Write values to multiple cell
you should always be on the lookout for a way to enter in values programmatically rather than creating a list and entering them one by one
e.g. Row 1, col 1 = 1 x 1
Row 1, col 2 = 1 x 2
……
Row 2, col 1 = 2 x 1
Cut, copy, and paste cell data
There can only be one active cell at a time (the green border in a cell)
Select is when you highlight a range of cells
If you want to copy or cut a cell you need to select rather than activate a cell range
Macro assumptions:
- First, the worksheet from which we want to cut our data is the active sheet.
- Secondly, it assumes we have already selected the cells we want to cut.
- Third, it assumes that the active cell on the summary worksheet is the destination for the cut data.
For assumption 2 make sure you: select the range, copy but don't do anything, then Esc, before going to VBA editor to run the code
If you press ctrl z, nothing happens because we can’t undo VBA.
- For paste and paste special. If you want to use any of them, record a macro of you performing the action and use that code in your subroutines.
Find values in cells
xlWhole → is to look at entire worksheet
There are many find option (e.g. upper lower case), recommend to record a macro with exactly the settings you want and edit it to allow user input.
Refer to cells using the OFFSET function
You should give it consistent structure, so you know where each data point appears in relation to all of the others. A consistent structure means you can use the distance from one cell to another, called the offset, to refer to cells in foreign .xls and other constructions.
A1 is the initial cell:
1 row offset →A2
1 column offset →B1
2 column offset →C1
C3 is the initial cell:
-1 row offset →C2
-1 column offset →B3
Concatenate text strings
One great way to provide feedback, is to generate custom message boxes, that derive their content from the user’s choices. To create the text for those message boxes you can combine existing text, and variable contents for the final text.
Return part of a string
You can create your own function in excel using VBA code
Mid(“ file name”, start position, how many characters)
Manage worksheets with VBA
Worksheets(1).Activate → will activate the first sheet from left
Worksheets(“sheet2”).Activate →will activate the sheet name “sheet2”
Sheets.Add → adds a new sheet to the left
Sheets.Add after:=Worksheets(“Sheet2”) → adds new sheet after “Sheet2”
Sheets.Add after:=Worksheets(Sheets.Count) → count the number of sheets and add new sheet at the end
Worksheets(“Sheet3”).Name = “Test” — -> change sheet name
Worksheets(“Test”).Delete → when you run it, excel will prompt a display alert, but you can turn off in VBA:
Manage workbooks with VBA
- Workbooks(“Workbooks.xlsm”).Activate → to go to the workbook
- Workbooks(“Workbooks.xlsm”).Save → to save the workbook
- Workbooks(“Workbooks.xlsm”).SaveAs Filename:=”C:\Users\ASUS\Dropbox\#WenJie\linkedin learnig\VBA\Ex_Files_Learning_VBA_Excel\Ex_Files_Learning_VBA_Excel\Exercise Files\Ch05\WorkbooksBackup.xlsm” → to save as your file. the bold is the name you give to the save as file.
- Workbooks(“SalesData.xlsm”).Close → to close the specified workbook
Chpt 6 — Turn off screen updating when you run a macro
Screen will flash when you run macro, but you can turn off
Application.ScreenUpdating = False → screen will not flash everytime you run macro
Use worksheet functions in a macro
Acquire values using an input box or message box
vbYesNo →button “Yes” and “No”
Type:=8 → allows to select cells
Call a subroutine from another subroutine
- RandBetween
The RandBetween function generates a random number between two other numbers.
Chpt 7 — Run a procedure when you open, close, or save a workbook
Cancel = True → prevent the workbook from closing without meeting a specific condition
Run a procedure when a cell range changes
Worksheet_Change(ByVal Target As Range) →whenever you use Worksheet_Change just memorise this
= 1 means user chose “ok”
Application.EnableEvents = False → turn off event
Trigger a procedure using a specific key sequence
when you assign a function key to a macro, then that function key will always run the macro and not perform its normal behavior.
Chpt 8 — Capstone
End Select →used to end a case statement
Other sources:
- “Excel 2016 Power Programming with VBA”. by Michael Alexander and Dick Kusleika and John Walkenbach (mid-level introduction to programming in Excel VBA.)
- “Microsoft Excel 2019 VBA and Macros” by Bill Jelen and Tracy Syrstad. Microsoft Press (complement to Excel 2016 Power Programming)
- “Professional Excel Development” by Rob Bovey and several other co-authors (more serious programming audience)
