VBA (Visual Basic for Applications)

Create a subroutine - Learning VBA in Excel Video Tutorial | LinkedIn Learning, formerly Lynda.com

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)

you wont be able to find the commission function you created previously

• 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:

right click the shape →”Assign macro”

To assign macro to quick access tool bar, go to file →options →quick access toolbar →choose macro →Add. See below:

it will appear here

You can also add macros control to your ribbon panel:

You can rename the group. Then add “Display Tax” to the group:

it will appear here

just click remove to delete the macro

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

setting color to the tab

vbRed →visual basic Red

With End blocks

To increase your programming efficiency

Create macro to change font and size

For…Next loop

way to repeat code in Excel VBA

Step 2: will skip every one

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.

append “Test” to each worksheet name

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.

if letter was given instead of value, excel will treat as 0 and + 3

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.

put mouse on the left side and press beside the line

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.

The strCity is now “LA” as the immediate window defined it. *press “enter” to run the code in the immediate window

Type debug…then F5 to see the code output on the immediate window

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

To add a watch: Debug>Add watch

CurTotal: is the name we give the watch

Press F5 in the routine to watch the results

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:

1. First, the worksheet from which we want to cut our data is the active sheet.
2. Secondly, it assumes we have already selected the cells we want to cut.
3. 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

Declare variable as Variant means you declare as any data type

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.

Put most restrictive cases at the top first

key in the volume and click “Find Discount”

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:

You can off with False and on with True

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

show msg once you open the excel

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:

1. “Excel 2016 Power Programming with VBA”. by Michael Alexander and Dick Kusleika and John Walkenbach (mid-level introduction to programming in Excel VBA.)
2. “Microsoft Excel 2019 VBA and Macros” by Bill Jelen and Tracy Syrstad. Microsoft Press (complement to Excel 2016 Power Programming)
3. “Professional Excel Development” by Rob Bovey and several other co-authors (more serious programming audience)