Interactive Calculations Macro Using the Activecell and Offset Properties and the Inputbox Function
Today we will see how we can create an interactive calculations macro using the Activecell and Offset properties and the Inputbox function. Intuitively most people know what is an activecell. It is the cell which you have selected with the mouse or keyboard and is outlined dark and whose address is dispalyed in the name box. You can enter data only in the selected or active cell.
Offset just means how far, row-wise and column-wise, you are or want to move from an active cell. Offset takes two parameters. For example:
ActiveCell.Offset(0,1). This means that you wish to be in the same row but one column to the right of the activecell
ActiveCell.Offset(2,-3). This means you wish to move two rows below and three columns to the left of the activecell.
ActiveCell.Offset(-2,-1): here you wish to move two rows above and one column to the left of the active cell.
The general Syntax of the Offset property is:
Offset(RowOffset,ColumnOffset): RowOffset equals number of rows (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset downward, and negative values are offset upward. The default value is 0.
ColumnOffset: The number of columns (positive, negative, or 0 (zero)) by which the range is to be offset. Positive values are offset to the right, and negative values are offset to the left. The default value is 0.
Inputbox Function: Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box.
Let’s take a look at the macro’s code below:
Dim qty As Integer
Dim price, amount As Single
Range(“A5”) = “Item”
Range(“B5”) = “UnitPrice”
Range(“C5”) = “Quantity”
Range(“D5”) = “Amount”
ActiveCell = InputBox(“Enter the name of item”)
price = InputBox(“Enter the price”)
ActiveCell = price
qty = InputBox(“Enter the qty”)
ActiveCell = qty
amount = price * qty
ActiveCell = amount
After the name of the macro, we define the data type for qty (quantity), price and amount so that the right kind of data is transferred from the input-box to the Excel worksheet cells.
Next we assign where the headers or labels will be located. Once the input-box pops up and data is entered into it, it is transferred to the worksheet and using the offset property we move proper rows and columns away from the activecell. After the calculations we move again to the proper cell so that data entry can take place at the correct location under the correct labels.
IMPORTANT: You must position your cursor at cell A5 before you run the macro.
Let’s watch the video below and see the macro in action.