Excel VBA20 tips for effective macros

e-bookVeryFastExcel

Note: This free e-book is part of the online course:

“Excel Macros - a friendly and effective quickstart in VBA”.

Students will get lifetime access to:

over 140 minutes of HD video tutorials,

homework files,

real world and ready-to-use macros and much more.

Click below for a free preview:

https://www.udemy.com/course/excel-macros-a-friendly-and-effective-quickstart-in-vba

ContentWhat to do for macros to work in your Excel files

What if macros are automatically disabled?

1. Where is the record button?

2. Principles when recording a macro

3. Naming and saving a macro

4. How to run a macro

5. Best Shortcuts for working with Macros

6. Relative reference vs Absolute Reference – example

7. How to insert a module

8. How to start creating a macro from scratch

9. Object properties and methods

10. Objects + examples

11. How to declare variables + examples

12. Useful tips for working with code

13. FOR loop – syntax

14. If statements – syntax + examples

15. Obtain the last row in column A

16. Obtain the last column in the first row

17. Code to highlight in yellow

18. How to understand the = sign in VBA

19. Do… Loop – syntax + example

20. MsgBox and InputBox –

syntax with examples

Bonus tips

How to get the insider +80% discount

Before we start:

What to do for macros

to work in your

Excel files

• When you open a xlsm file, you sometimes can get

the below message. Please click on Enable Content

in order to be able to run the macros.

Before we start: What if macros are

automatically disabled?

• Go to File, Options,

Trust Center, Trust

Center Settings, Macro

Settings and make sure

that the second option

is selected: Disable all

macros with notification

(instead of the first one)

1. Where is the Record macro button?

• Option 1: in the View Tab

• Option 2: in the Developer Tab (make sure that first, make this tab visible):

2. Principles when recording a macro

• Click to Start Recording. Give the macro a name and save it

• Perform the operations

• Stop recording

• Then find the macro and run it – the macro will perform all the recorded steps in the same order in exactly the same range

Take-aways:

1. The macro will record fixed references, according to your selection

2. The macro doesn’t record the time spent between operations.

3. The macro will record any selection or scrolling.

3. Naming and saving a macro

• The name should start with a letter, should not contain special characters as

for example, spaces (blanks). Separate the words using underscores: _

• Two common options for saving a macro:

• In this workbook – you will use the macro only in the workbook

• In the Personal macro workbook – you can use the macro in any open workbook

4. How to run a macro

• Five options are the most frequent:

• 1. Find the macro (use the shortcut Alt+F8), select it and click on Run

• 2. Run the macro with a shortcut (that you assign when you start to record).

Beware! The shortcuts for macro will override the existing keyboard shortcuts.

• 3. Insert a shortcut in the Quick Access Toolbar. Click the icon to run the macro

• 4. Insert a button in the worksheet and assign the macro to it. Then click on the button to run the macro

• 5. Use F5 or F8 inside the Visual Basic Editor

5. Best Shortcuts for working with Macros

• Alt + F8 – see all the macros

• Alt + F11 – open the Visual Basic Editor

• F5 – run the macro fully (from the beginning or from a specific point to the

end)

• F8 – step into the macro (run the macro line by line)

• F9 – insert a break (the macro will run up to the selected line)

6. Relative reference vs Absolute Reference -

example

• The activecell is A1. I start recording, I select A2 – then stop recording.

• See the difference in the code:

• Absolute reference → always selects A2

Range("A2").Select

• Relative → always selects the cell below the current cell

ActiveCell.Offset(1, 0).Range("A1").Select

7. How to insert a module

• Remember: to create a macro from scratch, you need to insert a module.

• Open the Visual Basic Editor

• Select the desired VBA project (the current workbook or the Personal macro

file)

• Go to Insert, Module

8. How to start creating a macro from scratch

• In the module, start typing Sub followed by the desired macro name

• Example: Sub format_file

• Then simply press enter

• The below code is now automatically created:

• Sub format_file()

• End Sub

• Start to type your macro instructions in between the two lines of code.

9. Object properties and methods

• Example of properties of the Range object: Value, Interior, End, Row

• Methods are like verbs – they perform an operation. Examples of methods

for the Range object: Cut, Copy, Select, Exportasfixedformat

Tip: objects like Range display the list of the possible Properties and Methods.

Simply type in the object followed by a dot (for example, Range(“A1”). )

10. Objects

• The most frequent ones are: application, workbooks, worksheets (and

sheets), columns, rows, cells and range

• We use as well: activecell and selection

• For a comprehensive list of objects, see the Article “List of useful objects in

VBA for beginners”

Example

of syntax

for objects

• Range (“A1”)

• Range (“A1:B3”)

• Rows (1:3)

• Columns (“A:C”)

• Columns (1:3)

• Cells (2,2)

• Sheets (“Sheet1”)

• Worksheets(1)

• Workbooks (“File name”)

• Workbooks(“This file”).Sheets(“Sheet1”).Range(“A1”)

11. How to declare variables

• Dim variable As variable type

• Tip: in order to use a variable for the entire module (and not for a specific sub), declare the variable before you start the first sub.

For a list of variable types (and how to select the most frequent ones), see the course resources (Lesson: How to declare variables in VBA. Types of variables. Write VBA code exercise.)

Example of declaring and initializing variables

• Dim i as Integer

i = 2

• Dim strg as String

strg = “John Doe”

• Dim i, j as Long

i = 2

j = Range("A1").End(xlDown).Row

12. Useful tips for working with code

• How to insert a comment: use ‘

• How to insert a block of comments: Go to View, Toolbars, select the Edit tab. Then use this icon:

• How to make the code more readable: use Tab

• How to continue to write a long line of code on the next row, without splitting it: use space followed by underscore, then hit Enter → (space)_

• How to see the list of variables: CTRL + space bar

13. FOR loop - syntax

Dim i as Integer (or Long)

For i = 2 to 10 (2 and 10 are just examples; we can use a fixed number of

another variable)

Instruction(s)

Next i

14. If statements - syntax

• Option A (everything in one line):

If condition Then Consequence 1 [Else Consequence 2]

• Option B (using an If Block)

If condition Then

Consequence 1

[Consequence 2]

Else

Consequence 3

End If

Example of FOR + IF

Dim i As Integer

For i = 2 To 10

If Cells(i, 3).Value = 0 Then

Cells(i, 4).Value = "No sales"

Else

Cells(i, 4).Value = Cells(i, 2).Value / Cells(i, 3).Value

End If

Next i

15. Obtain the last row in column A, that is

populated with data (before an empty cell)

j = Range("A1").End(xlDown).Row

16. Obtain the last column in the first row that

is populated with data (before an empty cell)

j = Range("A1").End(xlToRight).Column

17. How to highlight in yellow

Object.Interior.Color = 65535

18. How to understand the = sign in VBA

• In VBA, we write i = i + 1

• For =, instead of reading “equals to”, we read “we attribute“

• In our example, to the new value of i (left) we attribute i (current value) + 1

• So if i is currently 1, then upon executing the code i = i + 1 , we obtain →i = 1 + 1 → i = 2

Remember: the right part of the equation is executed first(highlighted in green)

19. Do… Loop - syntax

Dim i as Integer (or Long)

i = 2 (or any starting value)

Do

Instruction1

[Instruction 2 etc]

i = i + 1

Loop While condition

Do…Loop - example

i = 2

Do

If Cells(i, 5).Value = "" Then

Rows(i).Delete

i = i - 1

End If

i = i + 1

Loop While Cells(i, 1).Value <> ""

20. MsgBox and InputBox –

syntax with examples

• MsgBox “Welcome, user!”

• Dim i as Long

i = Range("A1").End(xlDown).Row

MsgBox “The total number of rows in column A, before a blank cell is encountered, is: “ & i

• Dim str as String

str = InputBox("Please enter the month to be deleted, in the format: .mm. ")

Bonus Lessons

21. Useful tips for the object Worksheets

• You can refer to a worksheet using his position (number), from the left to the right. Example: Worksheets(1)

• How to count the number of worksheets

• Worksheets.Count

• How to get the name of a worksheet

• Worksheets(1).Name

→ See all these tips in action in the lesson “Create PDF files in one click…” – RED section

22. How to Exit a sub. Sample code

• Use the words: Exit Sub

• The below macro tests if the input given by the user is not a number. If so, it exits the macro:

j = InputBox("Please enter the number of the last row: ")

'code to test and exit the macro when the entered input is not a number

If Not IsNumeric(j) Then

MsgBox "Please enter a number next time."

Exit Sub

End If

23. Safely delete rows – sample code

• Delete the rows with empty cells in column B. Check from row number 2 to row number 10

For i = 2 To 10

If Cells(i, 2).Value = "" Then

Rows(i).Delete

i = i - 1

End If

Next i

See all of these tips in action!

The full course includes lifetime access to:

• 140+ minutes of high-quality video content

• Excel project files (with solutions)

• Course quizzes to test your knowledge

• 1-on-1 expert support

• 100% satisfaction guarantee (no questions asked!)

FULL COURSE ACCESS

Excel Macros - a friendly and effective quickstart in VBA

Reach out to valentin@veryfastexcel.com and grab your insider discount.