Notice that this example uses the InputBox method instead of the InputBox function so that the method can perform type checking. This example uses the worksheet function Pmt to calculate a home mortgage loan payment. In the following example, the RAND worksheet function (which generates a random number) is assigned to the Formula property of range A1:B3 on Sheet1 in the active workbook. To insert a worksheet function into a cell, you specify the function as the value of the Formula property of the corresponding Range object.

Inserting a worksheet function into a cell For example, and Log will return different values. A function may have the same name as a Microsoft Excel function and yet work differently. Visual Basic functions don't use the WorksheetFunction qualifier. However, in a Visual Basic procedure, you would specify a Range object to get the same result. In a worksheet cell, you would enter a formula such as =MATCH(9,A1:A10,0). For example, you can use the Match worksheet function to search a range of cells. If you use a worksheet function that requires a range reference as an argument, you must specify a Range object. Set myRange = Worksheets('Sheet1').Range('A1:C10')Īnswer = (myRange) Finally, the value of answer is displayed in a message box.

Another variable, answer, is assigned the result of applying the Min function to myRange. First, the variable myRange is declared as a Range object, and then it is set to range A1:C10 on Sheet1. The following Sub procedure uses the Min worksheet function to determine the smallest value in a range of cells. In Visual Basic, the Excel worksheet functions are available through the WorksheetFunction object. Calling a worksheet function from Visual Basic For example, the Concatenate function is not needed because in Visual Basic you can use the & operator to join multiple text values. Some worksheet functions are not useful in Visual Basic.

Visual basic for excel cheat sheet