learn microsoft excel

Interactive Calculations Macro Using The Activecell and Offset Properties and The Inputbox Function

Excel 2007
Excel 2003
Home
Macros Excel 2003
Excel 2010
About Us
Free Ebook
Offline Training

Interactive Training



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:
Sub test()
Dim qty As Integer
Dim price, amount As Single
Range("A5") = "Item"
Range("B5") = "UnitPrice"
Range("C5") = "Quantity"
Range("D5") = "Amount"
ActiveCell.Offset(1, 0).Select
ActiveCell = InputBox("Enter the name of item")
ActiveCell.Offset(0, 1).Select
price = InputBox("Enter the price")
ActiveCell = price
ActiveCell.Offset(0, 1).Select
qty = InputBox("Enter the qty")
ActiveCell = qty
ActiveCell.Offset(0, 1).Select
amount = price * qty
ActiveCell = amount
ActiveCell.Offset(0, -3).Select
End Sub

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 Interactive Training