Code to change generate VBA Cell formula

[pingme89]

I am trying to use VBA to automatically take a cell formula and convert it to a string that can be used in VBA to place the formula in a cell.

Sorry for my poor explanation.
Basically I want to make a simple tool that someone could type in a formula into a cell, use VBA to convert it to a codified version of the formula.


For example: =IF(OR(A4=1,A5= 1),"Wired",IF(OR(A8= 1,A9=1),"Wireless"," "))

The VBA code for this would be to place in a cell would be: Sheets(1).Range("A1").formula = "=IF(OR(A4=1,A5=1)," & Chr(34) & "Wired" & ",IF(OR(A8=1,A9=1)," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & Chr(34) & "))"

I am trying to convert the former to the latter example automatically with VBA in Excel. I am going crazy trying to get it to work.
Anyone have any ideas on how to get it to work?

 

[offthelip]

this should work;

Sheets(1).Range("A1").Formula = "=IF(OR(A4=1,A5=1)," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A8=1,A9=1)," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "))"

When i am trying to solve these problems, I take out the ".formula" and the equals sign so the code looks like this:

Sheets(1).Range("A1") = "IF(OR(A4=1,A5=1)," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A8=1,A9=1)," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "))"

This just writes a string into the cells which you can then look at to see if it looks correct, when you think it is correct just edit it and add the equals sign , then if there is a problem with it excel will highlight exactly where the problems is

 

[pingme89]

Actually that is not what I mean. I am trying to take a formula from a cell and Translate it into a string that can be used within VBA to place that formula into another cell. The code would have to automatically separate the formula and add Chr(34). No formula in a cell would have Chr(34), and you can't just use quotation marks in VBA code. I am trying to de-compile back into VBA code.

 

[JonXL]

I don't really have a VBA solution, but I do have a formula that you can put into VBA and it will give you the same result:

Sub ConvertFormula()

'YOUR FORMULA IS IN CELL A1
'YOU GET YOUR NEW 'VBA-READY' STRING IN CELL A2

[A1].Value = "'" & [A1].Formula
[A2].Value = "=SUBSTITUTE($A$1," & Chr(34) & Chr(34) & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & Chr(34) & Chr(34) & Chr(38) & Chr(34) & " " & Chr(38) & " Chr" & "(34) " & Chr(38) & " " & Chr(34) & Chr(34) & Chr(34) & ")"
[A2].Value = "'" & [A2].Value
[A1].Value = [A1].Formula


End Sub

Maybe not the most elegant... but it works.

ETA: There are a lot of other characters you can't send directly into cell formulas (like &); this only works for quotes and those others might still cause problems...

 

[pingme89]

I am actually making the Forumula in cell A1 variable. I want the user to input any Formula and change it to VBA code in a string. I think your solution is Formula specific.

 

[Fluff]

Why not just do

Sub Frmla()

   Dim Fstr As String
   Fstr = activecell.Formula
   Range("C2").Formula = Fstr
End Sub

 

[JonXL]

It is not formula-specific at all.

Did you try it?

 

[Rick Rothstein]

Why not just do

Sub Frmla()

   Dim Fstr As String
   Fstr = activecell.Formula
   Range("C2").Formula = Fstr
End Sub

Or without the intermediary variable...

Sub Frmla()
  Range("C2").Formula = CStr(ActiveCell.Formula)
End Sub

 

[JonXL]

Why not just do

Sub Frmla()

   Dim Fstr As String
   Fstr = activecell.Formula
   Range("C2").Formula = Fstr
End Sub

That seems to do the same as:

[C2].Formula = ActiveCell.Formula.

I think pingme wants what amounts to a tool that automates the finding and replacing of quotes with Chr(34)s to turn formulas into strings that can be sent into cells through VBA.

 

[Fluff]

It does indeed do the same. The point is you don't need to add the quote marks.