Catégorie : Excel VBA Course

The Application.InputBox() method provides better input control and overall more options than the basic InputBox(). Its return value depends on the type specified. You can choose the input type to be a number, formula, cell reference, text, or Boolean value. If the user clicks Cancel, the method returns False, which you can use to control the flow of your program.

Like the simple input box, you can specify a default value and a title. The input type is set using the last parameter. Therefore, the examples below use named parameters for clarity.

Entering a Number

Here is an example where a number is requested (Type 1):

Sub EnterNumber()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A1").Value = Application.InputBox(Prompt:="Number:", Type:=1)
End Sub

Explanation:
You can run the EnterNumber() procedure through the Macro dialog box, accessible via the Excel ribbon under the View tab → Macros → View Macros. Alternatively, you can create a button on the worksheet and link it to this procedure.

If the user inputs a valid number, it is placed in cell A1. Valid examples include « 5.7 », « 5e2 » (which equals 5 × 10² = 500), or « 0.57 ». Invalid examples are « 5a7 » or « a57 ». Note that entering « 5.7 » might be interpreted as the date July 5 of the current year depending on regional settings.

If the user inputs an invalid number, a warning message appears, and the input box remains open until a valid number is entered, as shown in Figure 8.5.

Entering a Formula

An example to input a formula (Type 0):

Sub EnterFormula()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A1").Value = Application.InputBox(Prompt:="Formula:", Type:=0)
End Sub

Explanation:
Run EnterFormula() via the Macro dialog or a button. The user can enter formulas including cell references, which may also cover non-contiguous ranges.

Typing =SUM($A$10:$A$12) will insert this formula into cell A1, showing the calculated result. Pay attention to the use of absolute references with dollar signs.

If the user starts typing =SUM(, they can select the cells using the mouse or arrow keys before closing the parenthesis.

Entering an invalid formula results in an error message.

Entering a Cell Range

An example for entering a cell range (Type 8):

Sub EnterRange()
    Dim rng As Range
    ThisWorkbook.Worksheets("Sheet1").Activate
    Set rng = Application.InputBox(Prompt:="Cell range:", Type:=8)
    rng.Borders.LineStyle = xlContinuous
    Set rng = Nothing
End Sub

Explanation:
Run EnterRange() through the Macro dialog or a button. First, an object variable for a Range object is declared.

The user can input ranges by typing or selecting with the mouse. Non-contiguous ranges are also supported. Remember to use absolute references with dollar signs for non-contiguous areas.

The selected range is returned and stored in the object variable, allowing further actions such as formatting or calculations.

Additional Notes

Besides the types described above—

• 1 for numbers,
• 0 for formulas, and
• 8 for ranges,

there are other type codes available, for example:

• 2 for text input, and
• 4 for Boolean values.

The return value of the InputBox() function is always a string. The input field can be pre-filled with a default value, which helps guide the user or speeds up data entry. To make it clear what the user should enter, both a prompt message and a dialog title can be displayed.

In the following example, the user is asked successively to enter a text, a number, and a date:

Sub SimpleInput()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Dim userInput As String
    Range("A1").Value = InputBox("Please enter text", "Text", "Moyo")
    userInput = InputBox("Please enter a number", "Number", "2")
    If userInput = "" Then
        Range("A2").Value = ""
    Else
        Range("A2").Value = CDbl(userInput)
    End If
    userInput = InputBox("Please enter a date (D.M.YY)", "Date", "04.06.25")
    If userInput = "" Then
        Range("A3").Value = ""
    Else
        Range("A3").Value = CDate(userInput)
    End If
End Sub

Figure illustrates the result after the second call to the InputBox() function.

Explanation:
Each time, a dialog box appears showing the prompt text, a title, and a default value.

• If the user clicks OK, the contents of the input field are returned as a string.
• If the user clicks Cancel, an empty string is returned.

When the user is expected to enter a number, you should check the returned string and then convert it to a Double using the CDbl() function. The user can use the familiar decimal comma to separate fractional parts, depending on regional settings.

Similarly, when the user is expected to enter a date, you should check the returned string and convert it to a date value using the CDate() function.

The worksheet function IsFormula() has been available since Excel 2013. It determines whether a cell contains a formula. If this is the case, the formula of the cell can be retrieved and displayed using the various versions of the Formula property.

Below is an example where the range from A10 to A14 is checked for formulas. The formulas in cells A13 and A14 are displayed in all four property versions. 

Sub FormulaVersions()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A13").Formula = "=A10 + A11 + A12"
    Range("A14").Formula = "=SUM(A10:A12)"
    Range("A15").FormulaLocal = "=A10 + A11 + A12"
    Range("A16").FormulaLocal = "=SUM(A10:A12)"
    Range("A17").FormulaR1C1 = "=R[-7]C + R[-6]C + R[-5]C"
    Range("A18").FormulaR1C1 = "=SUM(R[-8]C:R[-6]C)"
    Range("A19").FormulaR1C1Local = "=R[-9]C + R[-8]C + R[-7]C"
    Range("A20").FormulaR1C1Local = "=SUM(R[-10]C:R[-8]C)"
End Sub

Sub DisplayFormulas()
    Dim i As Integer
    ThisWorkbook.Worksheets("Sheet1").Activate
    For i = 10 To 14
        If WorksheetFunction.IsFormula(Cells(i, 1)) Then
            MsgBox Cells(i, 1).Address & vbCrLf & _
                   Cells(i, 1).Formula & vbCrLf & _
                   Cells(i, 1).FormulaLocal & vbCrLf & _
                   Cells(i, 1).FormulaR1C1 & vbCrLf & _
                   Cells(i, 1).FormulaR1C1Local & vbCrLf
        End If
    Next i
End Sub

Figures:

 Addition formula in cell A13 – shown in four versions

Sum formula in cell A14 – shown in four versions

Notes:
For versions of Excel prior to 2013, the loop index i should run only from 13 to 14 because IsFormula() is not available. Additionally, the condition using IsFormula() should be omitted.

Sometimes you will see the notation Application.WorksheetFunction.IsFormula() used as well. However, the top-level object name Application can usually be omitted.

This section explains the differences and similarities among the cell properties Formula, FormulaLocal, FormulaR1C1, and FormulaR1C1Local using an example with various assignments:

Sub FormulaVersions()
    ThisWorkbook.Worksheets("Sheet1").Activate
    Range("A13").Formula = "=A10 + A11 + A12"
    Range("A14").Formula = "=SUM(A10:A12)"
    Range("A15").FormulaLocal = "=A10 + A11 + A12"
    Range("A16").FormulaLocal = "=SUM(A10:A12)"
    Range("A17").FormulaR1C1 = "=R[-7]C + R[-6]C + R[-5]C"
    Range("A18").FormulaR1C1 = "=SUM(R[-8]C:R[-6]C)"
    Range("A19").FormulaR1C1Local = "=R[-9]C + R[-8]C + R[-7]C"
    Range("A20").FormulaR1C1Local = "=SUM(R[-10]C:R[-8]C)"
End Sub

Explanation:
Each line calculates the sum of the numbers in the three cells from A10 to A12, but using different ways to assign the formula.

• The Formula property expects the formula to be assigned in English notation. Inside the string, either a calculation expression using the plus operator (+) or the worksheet function SUM() can be used. The spaces inside the formula string are added only for readability and can be omitted without affecting functionality.
• The FormulaLocal property expects the formula to be assigned in the local language notation—in this case, German. The calculation expression with the plus operator (+) remains the same as in English. However, the worksheet function must be written as SUMME() instead of SUM().
• The FormulaR1C1 property expects the formula in English notation but uses relative cell references based on the R1C1 reference style. Here, R stands for « Row » and C for « Column ». Relative positions are specified inside square brackets. For example:
  • RC refers to the current cell.
  • R[-1]C refers to the cell one row above the current cell.
  • RC[-1] refers to the cell one column to the left.
  • R[-1]C[-1] refers to the cell diagonally up-left from the current cell.
• The FormulaR1C1Local property expects the formula in the local language notation and also uses relative cell references, but with a different syntax. Here, Z stands for « Zeile » (row) and S stands for « Spalte » (column). Relative positions are specified inside parentheses (round brackets), not square brackets. The examples above for R and C apply analogously to Z and S notation:
  • ZS is the current cell.
  • Z(-1)S is the cell one row above.
  • ZS(-1) is the cell one column to the left.
  • Z(-1)S(-1) is the cell diagonally up-left.