CODEWITHSUNDEEP
excelvbavariablesformula
Jiminie.Glick
Jiminie.Glick

Reputation: 27

Excel VBA variable value equal to formula

I'm struggling with assigning variable values in VBA. I understand how to set myVar equal to an ActiveCell.Value like this: 

ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[7])"
myVar = ActiveCell.Value
MsgBox (myVar)

Is it possible to set myVar equal to a formula and skip the ActiveCell.Value line. I've tried this, but it does not work. 

myVar = "=SUM(RC[1]:RC[7])"

Thanks for your help.

Upvotes: 0

Views: 26521

Answers (3)

Peter Albert
Peter Albert

Reputation: 17515

If you don't need your formula to be relative to the active cell, you can also use the short [..] to evaluate a statement, e.g.:

MsgBox [SUM(B1:B7)]

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19087

I don't know what is a wider scope of your goal therefore I present idea which could be useful. However, it switches from relative reference of FormulaR1C1 into relative reference of Range.Offset() techniques.

myVar = WorksheetFunction.Sum(Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 7)))
MsgBox myVar

Upvotes: 2

Joseph
Joseph

Reputation: 5160

Yes, you can. You start with setting the myVar variable first, then apply it to a range (ActiveCell or any other Range object).

Dim myVar as String
Dim r as Excel.Range

Set r = ActiveCell

myVar = "=SUM(RC[1]:RC[7])"
r.FormulaR1C1 = myVar

Upvotes: 0

Related Questions