How can I learn macros in Excel? I have been asked this question many times by friends and colleagues.
I don't think I have a perfect answer for this, this is what I think.
Firstly, it is seldom good idea to pick up a book on Excel programming and start reading it from cover to cover.
I haven't seen this work so far, not only for Excel VBA but for computer applications in general.
It is better to learn when there is a specific task at hand.
Excel's macro recorder is a great way to begin: start the recorder, do the steps manually, and then examine the code.
The next step is to learn to modify and optimize the code generated from the macro recorder.
For example, if the step needed is to change the background colour of the cell A1 to yellow and put in the number 34 into it, the macro recorder generates the following code: Range("A1").
Select With Selection.
Interior .
ColorIndex = 6 .
Pattern = xlSolid End With ActiveCell.
FormulaR1C1 = "34" Range("A2").
Select This code can be simplified, the underlying ideas being: 1.
There is no need to select the cell 2.
There is redundant code that can be removed The above code can be written into 2 lines: Range("A1").
Interior.
ColorIndex = 6 Range("A1") = 34 The code is not only smaller but also more efficient: it does only the required tasks and nothing more.
Learning to code this way takes some practice.
Excel's VBA help is a great resource.
To learn about the use of a specific keyword, type the word, place the cursor over it and press the F1 key.
Sometimes it helps to just look at the example in the help first than read the details.
Once mastery of basic code is achieved, it is the right time to pick up a book on VBA coding and explore greater depths and possibilities.
VBA code is certainly not difficult to learn, but writing efficient code takes some practice.
Once you know VBA, it is fun too ! I have had great satisfaction playing with code and watching it do exactly what I want as I sipped coffee!