Entering Excel formulas from VB?

[frosty7700]

How do I enter an Excel formula into a cell from VB? I can get any string I want in there, but when I enter a formula, e.g. "=A2"...when I look at the spreadsheet rather than the result, "=A2" is still displayed. But it I double-click on it as if to edit and then tab out it calculates. What gives?

Help! Thanks in advance.

 

[JustinEzequiel]

Sub InsertFormula()
Worksheets("Sheet1&quot.Range("A1:B3&quot.Formula = "=RAND()"
End Sub

 

[xlbo]

And by way of an explanation, you are entering a text string into the cell. What is really going in is:
="=A2"

When you go into the cell to edit and come out, excel recognises the = and tries to make a formula out of it.

You need to use one of the range formula properties:
.Formula
.FormulaR1C1
.FormulaArray
depending on what kind of formula or referencing system you are using.
Array for array formulas, R1C1 for R1C style referencing and Formula for straight formulae
HTH
Geoff

 

[frosty7700]

That is what I am doing. Everything works except for one field where quotes are included, e.g. :

Worksheets("Sheet1&quot.Range("A1:B3&quot.Formula = "=FORMULAX(""x"&quot"

So the formula in Excel reads =FORMULAX("x&quot. Again, when I click in the cell and then leave it, it works fine.

 

[xlbo]

Use triple quotes
Worksheets("Sheet1&quot.Range("A1:B3&quot.Formula = "=FORMULAX("""x""&quot"
HTH
Geoff

 

[frosty7700]

Triple quotes causes a compile-time error. I even tried quadruple quotes...caused a runtime error.

 

[xlbo]

Is x a string or a variable - if it's a variable then you need
Sheets("Sheet1&quot.Range("A1&quot.Formula = "=formulax(" & x & &quot"
HTH
Geoff

 

[frosty7700]

x is a string. The formula looks for an actual string as an argument.

 

[uberpudge]

Using either of these worked for me:

Sub DumpFormula()
Dim rngCell As Range

For Each rngCell In Sheet1.Range("A1", "J10&quot
rngCell.Formula = "=M1*M2/M3+M4-M5"
Next
End Sub

Sub DumpFormulaAgain()
Dim rngCell As Range

For Each rngCell In Sheet1.Range("A1", "J10&quot
rngCell.Value= "=M1*M2/M3+M4-M5"
Next
End Sub



Does your formula contain quotes?
Example: =IF(ISTEXT(M1),"Text",IF(ISBLANK(M1),"Null","Number&quot)

If so, you would have to construct the VBA code for the formula a little differently.
Example: rngCell.Value = "=IF(ISTEXT(M1)," + Strings.Chr(34) + "Text" + Strings.Chr(34) + _
",IF(ISBLANK(M1)," + Strings.Chr(34) + "Null" + Strings.Chr(34) + _
"," + Strings.Chr(34) + "Number" + Strings.Chr(34) + &quot)"

Both examples worked for me.
Hope this helps,
Pete

 

[KyleS]

I think uberpudge is right (that's the line of thought I was using - but I don't have time to test it out...) but if you don't want to go to that extent, you could just put

Worksheets("Sheet1&quot.Range("A1:B3&quot.Calculate after the line where you assign the formula. Kyle

 

[xlbo]

What I mean is does x = "x" or does x hold a string (say x = "abc&quot ?

If x = x then I don't think you need any quotes round it (although I havn't had a lot of practice putting custom functions into cells with VBA). If it holds a character string then my last example should do it
HTH
Geoff

 

[LouBalleweg]

Hi frosty7700,
It may be that your problem is not in your code but the way you have Excel set up.

Click on "Tools", "Options", "View" and see if the "Formulas" check-box is checked. If it is, Excel will display the formula in a cell instead of the result.

Hope this helps,
LouBalleweg@cs.com

 

[xlbo]

If that was the case, it wouldn't disappear when he tabbed out
Geoff

 

[frosty7700]

X = "X". In other words, it's not a variable containing a string, it is a string itself. The formula/function will not work at all without the quotes around it.

 

[frosty7700]

It's starting to look hopeless. Whether I use "" or Strings.chr(34) to create quotes it dies. However, when I put the value in a cell and reference that cell in the formula, it reads it fine. So I guess it can take a variable containing a string. But that doesn't really help me.

Putting a "calculate" line in my code won't do anything since the formula contains a function call which requires an additional program to be running. While I have this program included in Excel so when I start it it automatically loads these, for some reason it doesn't actually load when I create an Excel page from VB.

 

[xlbo]

How about assigning a variable = "x" and using the syntax

mVar = "x"
Sheets("Sheet1&quot.Range("A1&quot.Formula = "=formulax(" & mVar & &quot"
Geoff

 

[Guest_imported]

just use this :

ActiveSheet.Cells(1, 1).Value = "=B1*B2"
ActiveSheet.Calculate

 

[xlbo]

All Too complicated - it's not that easy - it's a custom function and it's being entered from VB rather than VBA
G