Excel VBA Tips: Introduction
Within excel, there are two sides to the program. The “front end” is the worksheet area where data is entered and the “back end” is the visual basic editor side.
There are many different things that excel can do to help save time and allow you to become more productive. Why perform difficult, repetitive tasks, when you can get a compute to do it for you? This is the reason why you should want to use VBA.
How To Start
Initially, I knew nothing about VBA coding, and looking at examples on the internet was very daunting. There are many different commands that can become overwhelming. The best advice I can give is to start with small, simple tasks. For example, say you wish to insert a row.
1. Firstly, click on the developer tab in the ribbon.
2. Scroll over to the left of the toolbar and select “Record Macro”. When the box pops up, rename Macro1 or leave it as is and then press ok.
3. right-click on the row where you want to insert a row.
4. Scroll down and click on insert row. It will then enter a row. Go back up to the toolbar and click on “stop macro”.
5. On the toolbar, click on “visual basic”. This will then take you to the visual basic editor. It is here where the code is stored. Below is a screen shot of the VBA editor screen.
To find the code that was just created, double-click the folder called modules. You will see the module as it was named in step 2. The code will be shown in a window on the right.
6. The code is shown below.
I shall now describe what each line means.
Sub Macro1 – This line signifies the start of the macro.
‘Macro1 Macro – The apostrophe at the start of the line signifies that the line is a comment. This line is not run during the running of the code, and is simply there to help people understand what the code is doing. Anything can be written in comments.
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove – This line does two things – shifts the selection down and copies the format from the line above.
End Sub – Closes out the macro.
7. Once you’ve created the sub, you can then assign it to a shortcut so that you can perform the task with only a shortcut key combination. Click on the macros button, a window will pop up.
Click on options and a box will pop up, allowing you to select the shortcut key.
Once you have selected what your shortcut key is, you can then complete this function quickly. There are MANY different functions that you can complete, you just need to picture what the outcome of the function is and understand a way to get to the desired outcome.
In my next VBA post, I shall explain the power of the activecell command.
Come check out the directory for the rest of my excel tips!
Submeg
IT
SUBMEG 