Macro Training

Presented By:

Abhishek Rajoria

What is a macro?• Excel is object based model…

• Objects include things such as application, workbook, worksheet, cells• Each object has properties

• Color, border, value, name

Macros execute tasks according to instructions. Macros do not think…• Everything you do can be expressed in code (instructions); it is stored the

same way.• Each time you do anything in Excel the same action can be expressed in code.• You interact with excel through an interface that makes the code easier to

understand/intuit.• Choosing the “sheet1” sheet is expressed as; Sheets(“sheet1”).Select

Why would we use it?

• It saves time giving you more time to work on higher level tasks

• VBA can do things more quickly than you can.

• It standardizes processes, eliminates error due to deviation from instructions• It will do the exact same thing each time it runs

When would we use it? Not use it?

• When you are doing a routine task that you can write VERY good instructions for doing, you can likely automate that task by using VBA.

• We won’t use it when abstract thinking or problem solving is a necessary component of the task or when there are any new variables present in our current task.

Where are we able to access a macro/vba?

• Developer tab – first you show get access

• Record a macro button – in Developer tab

• Alt + F11 show the VBE; Visual Basic Editor

• Tour of VBA code area• Project tree, object properties, space for code

How do you create it? 1

• Recording• Button• Naming – no spaces; use underscores or caps• Save in a Workbook• Save in Personal workbook

• What is personal workbook• How to open it for the first time

• Once you save a macro it opens each time you open Excel• Always hidden.• If you unhide PW and close excel, PW will close!• To re-open; C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART – Application Data file is hidden!

• How to call the macro – from Developer tab• Save workbook as .xlsm or you will lose your work!

Standard R1C1

=A1+1 =RC[-1]+1

=$A$1+1 =R1C1+1

=$A1+1 =RC1+1

=A$1+1 =R1C[-1]+1

=Sum(A1:A10) =Sum(RC[-1]:R[9]C[-1])

=Sum($A$1:$A$10) =Sum(R1C1:R10C1)

• Module subroutines• Show what recording does

• Walk through example• Show excess that gets recorded and then how to spot

and clean

• R1C1 syntax vs. Regular• What is R1C1?• What does R1C1 syntax look like and how to convert?

(formula in B1)

How do you create it? 2

• Break mode• When running code produces error you get break mode to fix error and prevent

code from continuing or application running.• Sometimes nothing works! Maybe you’re in break mode?• How to tell and how to tell (in title and yellow highlighting)• How to get out of break mode.

• Notes in VBA code• How to write notes “ ‘ “• Why to write them• Break code into chunks by function• Record sections of code if you don’t know syntax

How do you create it? 3

• How to run code from VBE• Call Macro from Excel, F5 in VBA, or “run” from drop-down• How to break code if it keeps going

• Show from example

• How to create dynamic row length (create example where we see the problem to illustrate problem solving tactic)

• Declaring variables using Dim xxx as xxx• Substitute variable for specific reference in code

• Code upon a user actionIn VBA Editor select workbook object

• Change left drop-down box to “workbook”• Change right drop-down box to desired action• Place code within the newly create subroutine boundaries

How do you create it? 4

• Loops• For

Syntax and example• If

Syntax and example• Do While

Syntax and example

• Example of code/program• Buttons and things

• Where to insert buttons• How to assign code to buttons• Userforms

• Awesome but code gets more complex• Don’t forget objects have values – radio button.value can

be true or false

How do you create it? 5

• Macros run from wherever you call them if you call them on the wrong sheet the result can be really bad

• Use Sheets(“sheetname”).Select to make sure it always goes to the right sheet before you call it

• Usage of message boxes to check code – msgbox “stop”

• Problem: with filters being off and on and errors – they need to be correct or you’ll get errors

• Problem: with sheets being hidden and referencing them for actions

• Error handling• If it is possible that your code will generate an error you don’t want

your code to stop running. Need to tell it to do so b/c by default it stops. 2 methods:

• On Error Goto Next• On Error Goto 0

Useful Tips 1

• Syntax in VBA and Excel is not the same…. Of course• Use msgboxes to help see if your code is right• More quotes are needed.

• Create a UI with hiding sheets• Create codes that show and hide sheets so that when an action is taken

you go to a different part of a workbook. This turns a workbook into an application.

• Save before you run, extremely hard to undo macros

• The best code never uses the “select” code! Each time this happens it slows down the code. See if you can find ways to only use VBA.

• Example: Paste special values formula can be written as ‘Range(“A3”).Value = Range(“A3”).Value’

Useful Tips 2