A beginners guide to modifying Microsoft Excel Macros/VBA code for your own projects
In this guide we will explain how to modify Excel Macro code so that your macros should work for your own projects and purposes. VBA code can seem daunting for beginners but it can also be outlined in a simple fashion.
In this example we are going to use the following VBA code as our example to give you a better idea as to how this is done.
Sheets(“Sheet1”).Range(“A4”).Select
ActiveCell.Formula = “=1+1”
In this code, the macro first selects Cell A4 from the sheet named Turkey and then inserts into that cell the formula “=1+1”. This is going to be our example.
Starting out with our first line of VBA code, this line essentially selects a specified cell in a specified sheet, and is a very common occurrence in VBA code. You can see that this is separated into “Sheet” and “Range”.
What is an Excel sheet?
A sheet is essentially a page within a workbook. By default they are named Sheet1, Sheet2 and Sheet3 as seen in the image below.
Sheets are commonly renamed to be more descriptive to their purpose, and a renamed sheet needs to be reflected in any macro code so the code knows where to look within the workbook. Take the following example where the sheet has been renamed to “Turkey”.
If this was our working example, our first line of code would need to read as follows.
Sheets(“Turkey”).Range(“A4”).Select
You will need to similarly edit your own code within your own macro/VBA code.
What is an Excel range?
An excel range is a single cell or selection of cells, and in our example it is the cell we want the macro to target and apply a formula.
A range can be a single cell such as in our example above. I.e. A4. A range can also be a selection of cells such as A1:A4. A1 to A4 would include all cells from that range. In this case it would be A1, A2, A3 and A4. Depending on your requirement you will want to specify whether the range is a single cell or a selection of cells for your needs.
Once your selection has been made you will then want to specify what action the macro performs on your targeted cells.
Applying defined functions to targeted cells
In our example, the following code is used (i.e. the second line of VBA code)
ActiveCell.Formula = “=1+1”
This piece of VBA code essentially tells the macro that for the targeted cells, apply the following formula of =1+1. This is another common theme or occurrence in VBA code so you may see in other applications that a similar target and application is used.
If your example is to apply a formula to a cell, using the same code you would simply alter the formula between the parentheses. For instance if the formula you wished to enter into a cell was =2+2 your VBA code would read as follows.
ActiveCell.Formula = “=2+2”
This guide should have given you a basic understanding of how to edit prewritten VBA code to your own project workbooks for the cells you wish macros to target and work upon. If you have any questions then please feel free to put them in the comments below.
