Auf das Leben!

I solemnly swear that I am up to no good.

Useful Tips for Excel VBA

Recently I’ve been working intensively with Excel VBA to achieve some automation of data processing and analysing. During my first approches to use VBA, I found some really useful tips for newbies in this area.

1.Think Before Coding.

This is definitely the iron-rule of coding, regardless of the programming language. You have to fully understand what problem are you solving, which function or method do you need exactly for this problem. I will introduce some useful steps for clearing the problem and also get to know what you are really facing in the following text.

2.Use Immediate Window

This is a useful function that embedded in Excel. You can get instant result of your command in this window, therefore, it is a really good friend while you are debugging.

3.Use Debug.Print

If you don’t know what results your stated formula in VBA will get, this is the one command you should turn to for some help. Just simply type in the command and add the variable you want to know after the command.

Useful Debug
1
Debug.Print Variable

4.Always Break Your Work Into Smaller Pieces

I will show you how this works through a really simple example. My task would be add a button into the worksheet, it can achieve the function of copying certain range of values and paste it somewhere. I will first disassemble this task into 3 steps.

1.Write code about the function of copy and paste

2.Add a button to the worksheet

3.Assign the function to the button

Of course, you can further disassemble step one, maybe first learn about the knowledge of “Range” object. It depends on your level.

5.Build Iteratively To Fully Understand The Task

Maybe for people not familier with programming, this is a good approach. For iterative problems, we have to consider all the conditions and loops. With listing some of them without loop, you can get a better and straightforward view of your task. This can save a lot of work if you write your loops wrong and debugging afterwards.

6.Refer Not Only to Internet, But Also Refer To A Good Book

Internet is a useful tool for some instant problems. But if you want to use VBA for a long term, you may better invest more time on learning it through a book. To be honest, I don’t really have that much time to read the book, but I’ve found some really useful conceptural knowledge in the following book.

Excel Power Programming with VBA by John Walkenbach

7.Always Optimize Your Code and Speedup

Optimization
1
2
3
4
5
6
7
8
9
10
11
12
13
14
turn off some Excel functionality so your code runs faster
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False      note this is a sheet-level setting
  
Your code here
  
Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState

Comments