Microsoft Excel provides formulas to help you analyze data on a worksheet or to perform operations on sets of data on multiple worksheets. When you write code, you can choose to use a formula in the language of your user, or you can choose to use a formula in a language that's familiar to you and have Visual Basic convert the formula to the language of your user.
You can use the Formula property to enter a formula in a cell. If the formula you enter contains worksheet functions, Microsoft Excel converts the functions to the appropriate language. For example, suppose you use the Formula property to enter a formula in cell A1 on Sheet1, as shown in the following code.
ThisWorkBook.Worksheets("Sheet1").Range("A1"). _
Formula = "=SUM(A2:A3)"
When Microsoft Excel enters the formula on the worksheet, it automatically converts it to the equivalent formula in the language that's been set for the workbook.
If, instead, you write your code so that the user enters formulas in input boxes, Visual Basic still converts worksheet functions in the provided formulas, but you must use the FormulaLocal property instead of the Formula property. The FormulaLocal property converts the worksheet function the user types in the input box to the equivalent formula in the language that's been set for the workbook. The following example displays an input box and then inserts the formula the user supplies into cell A1 on Sheet1.
FormulaText = InputBox(EnterFormulaMessage)
ThisWorkbook.Worksheets("Sheet1").Range("A1") _
.FormulaLocal = "=" & FormulaText
If you subsequently open the workbook on a computer that's running the German version of Microsoft Excel, run the preceding example, and type sum(a2:a3) in the input box, the following formula appears in the formula bar.
= SUMME(A2:A3)
The NumberFormat property is used to set the format code for a range on a worksheet. The format code is the string that appears in the Category box on the Number tab in the Format Cells dialog box. The following example sets the format code for cell A17 on Sheet1 to "General."
Worksheets("Sheet1").Range("A17").NumberFormat = "General"
The NumberFormatLocal property sets the format code for the object in the language that's been set for the workbook. The following example displays a message box showing the number format for cell A17 on Sheet1 in the language that's been set for workbook.
Msgbox "The number format for cell A17 is " & _
Worksheets("Sheet1").Range("A17").NumberFormatLocal