Assigning the value of a formula to a variable

Question

I am learning coding in vba and I recently encountered a serious problem: I cannot assign the value of a formula to a varialbe without putting the variable to a cell first.

My point is that I need those value to do some other task, but I don't want to to put those formula to a cell (if I do, it will takes a lot more coding).

I did try this:

Dim Total As Integer
Total = FormulaR1C1 = "=sum(R1C1,R3C2)"

But I realize that this only return bolean value which obviously not my attention

So my main question here is: How can I assign the value of a formula (in string or integer type) to a variable

In addition, as using FormulaR1C1 requires a refernce cell, do I need to select a cell before assigning to make it work

Solution

There is one option which could be interesting for you. You could call SUM worksheet function calling it from VBA through WorksheetFunction object. How it works?

Based on your example you could get sum of your R1C1 & R3C2 (A1 & B3) this way:

Dim Total As Integer
Total = WorksheetFunction.Sum(Range("A1,B3"))

As you can see it is required to use Range object referenced to cell which you need to sum up. You can't use R1C1 references here. But I think it's not a problem.

One more tip. It's allowed to call it also this way:

Dim Total As Integer
Total = Application.Sum(Range("A1,B3"))

The biggest difference of two above examples is the way of error handling. Working with WorksheetFunction- any error will stop your procedure, working with Application-error will be thrown as a text without procedure brake.

The other tip- if you write WorksheetFunction. (dot at the end is essential) you'll get list of approx 200 function available for you to use. If you start with Application. there will not be a list of function but just methods and properties of Application Object.

Finally, see this link for more information regarding using WorksheetFunction.

EDIT One more thing, this could be obvious but make it clear. To sum continuous range from A1 to B3 you need just small change:

Dim Total As Integer
Total = WorksheetFunction.Sum(Range("A1:B3"))