Databases Reference
In-Depth Information
FIGURE 4.4
Choosing a function in Excel.
A dialog will pop up, which shows the most recently used functions. If the user selects “More
Functions . . .”, then the dialog box in Figure 4.4 will appear, which allows the user to choose any
function present in Excel (both built-in functions or VBA functions).
Sub AddFormula(wkbook, wksht, row, col, formula$)
'Subroutine adds formula$ to the specified cell in a Worksheet
ActivateWorkbook (wkbook)
Worksheets(wksht).Select
'Ensure formula starts with "=" !
If Left(formula$, 1) <> "=" Then
formula$ = "=" & formula$
End If
Range(Cells(row, col), Cells(row, col)).Select
ActiveCell.formula = formula$
End Sub
This subroutine will add the formula passed to it to the specified location (cell). The subroutine
checks to make sure that the passed formula string begins with “
.” A common mistake when
specifying formulas is to omit the equals sign. The following is the code for the improved version
of the subroutine
=
, which will write the appropriate formula into column E.
TimesTable
Sub TimesTable(ByVal Number As Integer, wkbook, wksheet)
'Create a Simple Times Table
Dim row As Integer, formula$
For row = 1 To 12
Workbooks(wkbook).Worksheets(wksheet).Cells(row, 1) = row
Workbooks(wkbook).Worksheets(wksheet).Cells(row, 2) = "x"
Workbooks(wkbook).Worksheets(wksheet).Cells(row, 3) = Number
Workbooks(wkbook).Worksheets(wksheet).Cells(row, 4) = "="
'Build Function String
formula$ = "=A" & Trim(Str(row)) & "*C" & Trim(Str(row))
Call AddFormula(wkbook, wksheet, row, 5, formula$)
Next row
End Sub
