Pmt(loanInt / 1200, loanTerm * 12, loanAmt)

Payment = Application.WorksheetFunction _ (Prompt:='Term in years (30 for example)', _ (Prompt:='Annual interest rate (8.75 for example)', _ The Static statements cause Visual Basic to retain the values of the three variables these are displayed as default values the next time you run the program. 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.

Learn visual basic for excel kindle