So you’ve gotten to the stage where you need a macro in your model. That’s great! Are you sure you need to use one though? If you need to automate anything to prevent getting a circularity, then that’s absolutely understandable. Or if you trying to automate some other task, like a goal seek… totally understandable too.
Some of the simplest advice I can give around macro’s is – make sure you absolutely need them! Now, why do I say this?
Macro’s get a bad rap (not a bad wrap, by the way, that’s more like when Chilango’s had a salmonella scare). And that’s because they’re generally the opposite of what you and I value in best practice models… which is transparency and flexibility.
But if you absolutely must have a macro, here are some guidelines in doing a basic copy paste macro. And yes, it starts with recording a macro, this is a great way to understand how actions translate from Excel into VBA (and so that you can understand how they translate back from VBA into Excel!)
Record a macro
View >> Macros >> Record Macro
- Select area to copy
- Copy
- Select area to paste
- Paste special >> values
Result should look like this
Refine the macro
We can replace the “.select” in both cases directly with the instructions to copy or to paste special.
We can also remove the extraneous detail around the paste special to just leave the “PasteValues”. This looks like this:
This makes the macro run faster – and is more clear to the user.
Automate with a Loop
The next step is to create a For Loop. We can also name the ranges of the J10:M10 to make the macro more robust.
Change to a Do Until Loop
Note; this is interchangeable with the “Do While” loop – however Do While syntax will be while the value is <> 0
Setup a Delta between the sum of the copied values and the sum of the pasted values. Name this Delta (see here “Macro_IntDelta”)
Speed up and refine with the Direct Copy
Instead of a “copy” and “paste” setup, we can more directly assign the properties of one range to another. The properties we’re assigning are values.
To do this, we need to assign the Paste Range to be equal to whatever the Copy Range is.
The below screenshot shows both Step 4 and Step 5.
So that’s pretty much it, in getting to an “intermediate” level of knowledge with macro’s. Is it truly intermediate? Well maybe not quite, because you may not have the feeling of what changes what in a macro… or how you can trouble shoot etc.
But these steps (especially the DO LOOP, and refining with DIRECT COPY are steps that a lot of even quite good financial modelers miss).
Good luck!
