Interactive Calculations Macro Using
Activecell and Offset Properties and The Inputbox Function
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 inputbox to the Excel worksheet cells.
Next we assign where the headers or labels will be located. Once the inputbox 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.
|Excel 2007||Excel 2003||Macros Excel 2007||Macros Excel 2003||Excel 2010|
|About Us||Free Ebook||Offline Training||Blog||Interactive Training|