Interactive Calculations in Excel Using VBA

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:
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 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.


One thought on “Interactive Calculations in Excel Using VBA

  1. Michael Diamond

    Hi Dinesh,

    I modified the code slightly using the With statement. I did find a bug in the code when i ran it. It seems that range A5 becomes bland when the second input box pops up. I can’t figure out what part of the code is doing this. Can you tell where why that is happening. You will notice that I add Item several times in the code. This is to correct this error. Can’t figure out why this is happening.

    Also, can you show me how I could put this statement inside of a loop so when the user presses a button of some sort it will exit the procedure. thank you in advance.

    Sub CalculationTest()
    Dim qty As Integer
    Dim price, amount As Single

    Cells.Clear

    Range(“A5”).Select

    With ActiveCell ‘ Add headings to Range A5:D5
    .Offset(0, 0) = “Item”
    .Offset(0, 1) = “Unit Price”
    .Offset(0, 2) = “Quantity”
    .Offset(0, 3) = “Amount”
    .Offset(1, 0).Select ‘ Move cursor to A6
    .Offset(1, 0) = InputBox(“Enter the name of the item”)
    .Offset(1, 1).Select ‘Move to Range B5
    .Offset(0, 0) = “Item”
    price = InputBox(“Enter the Quantity Price”) ‘assign variable to first input box
    .Offset(1, 1) = price
    .Value = price ‘enter price in B6
    .Offset(1, 2).Select
    .Offset(0, 0) = “Item” ‘ Heading disappears and this is to put it back
    qty = InputBox(“Enter the Quantity”) ‘assign variable to second input box
    .Offset(1, 2) = qty
    .Offset(0, 0) = “Item” ‘ Heading disappears and this is to put it back
    amount = price * qty ‘ assign variable to the product of the two input boxes
    .Offset(1, 3).Select
    .Offset(0, 0) = “Item” ‘ Heading disappears and this is to put it back
    .Offset(1, 3).Value = amount
    .Offset(1, 1).Select
    .Offset(0, 0) = “Item” ‘ Heading disappears and this is to put it back
    .Offset(1, 0).Select
    End With
    End Sub

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *