xlf EandA series


InputBox demonstration code


A simple code exercise. Time allocation 15 minutes.


0. Preliminary


The EXERCISE - complete the following



The InputBox method is sometimes used to assist with data entry and other actions performed by the spreadsheet user.


Required: Write a demonstration macro to illustrate the use of InputBox method to allow selection of a range object in the worksheet. When activated, the InputBox will show the address of the CurrentRegion. Pressing the OK button will show a box with the sum of the numbers in the returned range. Use the WorksheetFunction SUM to accumulate the values. Assume all values are valid. Pressing Cancel will exit the procedure.


 

1. Tasks


This is a suggested coding solution


DemoIBCR macro


The task is a VBA based application of material covered in session 9.



Code 1: the DemoIBCR macro interpretation of set task 
Sub DemoIBCR()
Dim tmp As Range
Dim rng_addr As String

    rng_addr = ActiveCell.CurrentRegion.Address
    On Error Resume Next
        Set tmp = Application.InputBox("Select range to sum", "SUM app", rng_addr, , , , , 8)
    On Error Goto 0
    If tmp Is Nothing Then Exit Sub

    MsgBox "The Sum of range " & rng_addr & " is: " & WorksheetFunction.Sum(tmp), vbOK, "SUM app"

End Sub