• Home
  • Other Stuff
  • Contact
Black Tor

VBA: A Few Tips

Simple VBA

Excel VBA

Below is some simple VBA code which you can use for a variety of purposes. Starting with these simple building blocks it is possible to build both your confidence, knowledge and ability to work with VBA completing complex appearing sub-routines.
Please revisit the site over the next few days and weeks as more content and helpful code is added.

Use a Formula In Your Code

The code below shows an example of how to use a formula within your subroutine and then assign the result to a variable which can be used later in your code or fed back to the workbook user as a result.
Here is the code...

Sub FormulaInCode()
' Use a formula or WorksheetFunction within your subroutine
Dim lAnswer As Long
lAnswer = Application.WorksheetFunction.Sum(Range("a1:a10"))
' use the result in your code or provide back to the user
MsgBox "The sum result is " & lAnswer
End Sub

This utilises a simple worksheet function within the VBA code. This could be used for a number of different purposes within a larger more complex VBA project.

Return to the  TOP Up Arrow

Place a Formula in a Cell

In this short example we create a formula and place this into a range of cells within the worksheet. As the $ sign has not been used within the code the range being referred to by the formula will alter with each reiteration of the formula on the worksheet.
Here is the code...

Sub FormulaPlacedInCell()
' place a formula into a range of cells
' without using the $ symbol this formula will alter for each cell it is placed into
ActiveSheet.Range("B1:B4").Formula = "=SUM(A1:A10)"
End Sub

More complex formulas can be used or single cells when necessary.

Return to the  TOP Up Arrow

Use R1C1 in a Formula

In this below example we use the row and column number references within the formula to refer to cells. In this example the formula created refers directly to the range and will appear as "SUM($A$1:$A$10)". A peculiarity of Excel is that in code we refer to the range in row then column order, but in formulas this order is reversed.
Here is the code...

Sub UseR1C1Fixed()
' use the row and column references
' this specifies the exact ranges which does not alter across the range
ActiveSheet.Range("B1:B4").FormulaR1C1 = "=SUM(r1c1:r10c1)"
' when examined the formula will appear to have the $ symbol
End Sub

It can be difficult initially when attempting to use the R1C1 references because of the basic difference with formulas which most Excel users started with.

Return to the  TOP Up Arrow

R1C1 Relative to Cell

In this example we demonstrate the use of ability to refer to cells that surround the cell which will contain the formula. In many cases formulas will not always be placed into the same rows or columns as processes are repeated. This means that the use of formulas that refer to exact ranges can produce errors. By surrounding the row or column referenced number with square brackets we refer to a row or column in a relative position to the cell containing the formula.
Here is the code...

Sub UseR1C1Relative()
' refer to fixed positions and positions relative to the cell containing the formula
ActiveSheet.Range("B1:B4").FormulaR1C1 = "=SUM(R1C[-1]:R[9]C[-1])"
End Sub

.

Return to the  TOP Up Arrow

Updating

Working on it!

Currently this site is under re-construction and this page amongst many is constantly being updated with information. Please revisit the site over the next few days and weeks as we add more content.