Syntax for using variables within VBA formula functions

S

sagain2k

Board Regular
Joined
Sep 8, 2002
Messages
94
I'd like to learn the rules for correctly using variables within functions used in formulas specified by VBA. Here's an example:

I want to place a formula within a workbook file called "Books 2003.xls" that goes to the version of the file for the previous year (or whatever year is specified) and does a Sum of a particular range (where that range is also specified as a variable) so it sums up the Total for the previous year for the same number of months that have data in them to date for the current year. Once VBA places the formula where it belongs, it should be able to always provide a running comparisson with the current year to date total and the totals for the same period for the previous year. Here's an example of the intent of this simple formula:

ActiveCell.FormulaR1C1 = "=SUM('[Books 2002.xls]Income Summary'!R4C2:R8C2)"

The workbook files will all be named the same way: Books 2002.xls, Books 2003.xls etc.... The range to be summed will always start at R4C2, but could then end anywhere from R4C2 (same as B4) up to R15C2 (same as B15).

I already have a routine that captures the value for the variable CurrentYear in the form "2003" and thus have another variable for PrevYear (= CurrentYear -1), and so then have a way to correctly specify the variable "BookName" to get the correct filename needed. I also have a variable for the CurrentMonth in the form of "1 through 12", and can use that to specifiy the correct RowNum needed to determine the end of the range (RowNum = CurrentMonth + 3).

I've tried to substitute the variables "BookName" and "RowNum" into the above formula with various combinations such as what follows, but I'm not getting the correct syntax with the right number of quotes etc...:

ActiveCell.FormulaR1C1 = ""=SUM('[" & BookName & "]Income Summary'!R4C2:R" & RowNum & "C2)""

This doesn't work, and I'd love to learn the correct rules for syntax when inserting variables into situations like this.

How do I insert variables into formula functions like this one? Any suggestions most welcome! Of course, there's probably even a better way to specify a formula, and I'm open to any approach. Thanks!
 
Sort by date Sort by votes
Hi sagain2k,

First here is a corrext code
Code: 
Dim BookName As String
BookName = "Books 2002.xls"
ActiveCell.FormulaR1C1 = "=SUM('[" & BookName & "]Income Summary'!R4C2:R8C2)"

The rules are easy. Just enclose by "double quote".
And if double quote is contained in the string, you make it as "".

Eg,
Code: 
Sub Macro1()
    Dim sampleString
    sampleString = "Hi There"""
    MsgBox sampleString
End Sub

You can only one " in a msgbox.
 
Upvote 0
Colo...Thanks so much for simplifying it for me! Worked like a charm!

Here's what I ended up with that worked just the way I wanted it:

BookName = "Books " & PrevYear & ".xls"
RowToDate = CurrentMonth + 3
EndRange = "R" & RowToDate & "C2"

ActiveCell.FormulaR1C1 = "=SUM('[" & BookName & "]Income Summary'!R4C2:" & EndRange & ")"
 
Upvote 0
Hi,
I'm running into difficulties using variables in formulas. I'm very new to vba. The following code seemed correct to me, but my compiler tells me the line in red is wrong.

Thanks in advance for the help



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim place As Integer
Dim col As Integer
col = Target.Column
Dim r As Integer
r = Target.Row
If Target.Column = 1 And Sh.Name <> "Template Guidelines" And Sh.Name <> "Summary" Then
Sheets("Summary").Select
Cells(2, 1).Select
Selection.EntireRow.Insert
ActiveCell.FormulaR1C1 = ""=Requirement_Traceability_Matrix!R["& r &"]C""
Selection.AutoFill Destination:=Range(Cells(2, 1), Cells(2, 5)), Type:=xlFillDefault

End If
End Sub
 
Upvote 0
I realize this is an old thread, but maybe someone's still out there. Given that ModelType is set to "FGs", why does the second line work, but the first does not? The first does not create a range name.

ActiveWorkbook.Names.Add Name:="Links" & ModelType & "Lookup", RefersToR1C1:= _
"='Links[" & ModelType & "]'!R6C1:R" & LastRow & "C5"

ActiveWorkbook.Names.Add Name:="Links" & ModelType & "Lookup", RefersToR1C1:= _
"='LinksFGs'!R6C1:R" & LastRow & "C5"
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Converting formula to use spill method
Replies
7
Views
542
Eric W
Eric W
R
  • Question Question
VBA to automate calculation
Replies
1
Views
262
HighAndWilder
H
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
24
Views
2K
MARK858
MARK858
W
  • Solved
Assigning a file and path to a variable within VBA to be inserted as a formula
Replies
1
Views
835
Woftae
W
N
  • Solved
VBA Use of SUM function with variables
Replies
8
Views
2K
Joe4
Joe4

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top