Style References

The A1 style uses the column and row headings (letters and numbers, respectively) as indices to reference a particular worksheet cell (for example, A1, B5, $C$2, etc.). Dollar signs in front of an index denote an absolute reference; the lack of a dollar sign on an index denotes a relative reference. The A1 style reference is the preferred style of most Excel users.

Creating a formula using VBA is easy. Instead of using the Value property of the range returned by the Cells property, you use the Formula property and assign a string value. The string should be in the form of an Excel formula.

In reality, you can also assign formula strings to the Value property of a range; however, it makes your code easier to read if you use the Formula property when assigning formulas to a range.

The following example inserts a formula in cell A11 of a worksheet that calculates the sum of the values in the range A2:A10 using the Excel application's SUM() function.

Dim formulaString As String formulaString = "=SUM($A$2:$A$10)" Cells(11, "A").Formula = formulaString

If you want to create a set of related formulas in a column, you can use a looping structure to iterate through the cells that receive the formula. The following example uses formulas inserted into the cells of column B in a worksheet to calculate a running sum of column A.

Dim formulaString As String Dim I As Integer

Cells(1, "B").Value = Cells(1, "A").Value For I = 2 To 10

formulaString = "=A" & Trim(Str(I)) & "+B" & Trim(Str(I - 1)) Cells(I, "B").Formula = formulaString

Next I

Looping through the cells is not the most efficient method available in VBA for inserting formulas. Using loops to insert formulas can slow your program down considerably, especially if it is running on an older machine with a relatively slow processor. You would not enter individual formulas in the Excel application when it is possible to copy and paste, so why do it with your VBA code? Instead, you can use Copy() and Paste() or AutoFill() methods that run much faster.

Dim formulaString As String Dim I As Integer

Cells(1, "B").Value = Cells(1, "A").Value formulaString = "=A2+B1"

Cells(2, "B").Formula = formulaString

To use the Copy() and Paste() methods, first insert the formula in the original cell as before, execute the Copy() method on the range returned by the Cells property, select the desired range, and paste the formula.

Range("B2:B10").Select

ActiveSheet.Paste

A method is yet another type of procedure that performs a specific action on a program component or object. The Paste() method performs its action on an Excel worksheet by pasting the contents of the clipboard onto the worksheet.

Another option is to use the AutoFill() method by specifying the destination range. The term Destination is a named argument predefined for the AutoFill() method in VBA. Named arguments allow the programmer to pass values to a function without having to worry about the order of the arguments, or how many commas must be included for optional arguments that are not used. Use the named argument operator (:=) to assign the value to the name.

Cells(2, "B").AutoFill Destination:=Range("B2:B10")

Or, if you prefer, you can still pass the arguments in a list.

Cells(2, "B").AutoFill Range("B2:B10")

The second line of code using the AutoFill() method works because Destination is the first argument/parameter that must be passed to the method. (As it turns out, the Destination argument is the only required parameter of the AutoFill() method.) Using the named argument with the named argument operator makes the code more readable; therefore, the first example with the AutoFill() method is probably better. You can use named arguments with any procedure in VBA.

Specifically, the Copy() and AutoFill() methods associate with the Range object returned by the Cells property, and the Paste() method associates with the Worksheet object. I'll discuss these objects in detail in the next chapter.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment