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?

 

[JonXL]

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

Some excel formulas use quotes by necessity.

For example:

=INDEX(D:D,MATCH("home",C:C,0))

To send that to a cell with VBA, you need to send:

"=INDEX(D:D,MATCH(" & Chr(34) & "home" & Chr(34) & ",C:C,0))"

 

[pingme89]

Yes, I tried, but the result wasn't what I needed.

For example, in A1 I have: =IF(OR(A4="Yes",A5= "Sure"),"Wired",IF(OR(A9="No",A10="Nah"),"Wireless"," "))
With your code, I get: '=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " & Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" & Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & ")," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "))

I want the result to be: ""=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " & Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" & Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & ")," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & Chr(34) & "))" "

The result is close. Notably the last "" at the end of the formula should be Chr(34) & Chr(34) and I want the whole result as a string. The result from your code made the result start with '. Can you tweak your code to make it work?

 

[Fluff]

Some excel formulas use quotes by necessity.

For example:

=INDEX(D:D,MATCH("home",C:C,0))

To send that to a cell with VBA, you need to send:

"=INDEX(D:D,MATCH(" & Chr(34) & "home" & Chr(34) & ",C:C,0))"

My approach works with that formula

 

[JonXL]

Yes, I tried, but the result wasn't what I needed.

For example, in A1 I have: =IF(OR(A4="Yes",A5= "Sure"),"Wired",IF(OR(A9="No",A10="Nah"),"Wireless"," "))
With your code, I get: '=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " & Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" & Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & ")," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "))

I want the result to be: ""=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " & Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" & Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & ")," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & Chr(34) & "))" "

The result is close. Notably the last "" at the end of the formula should be Chr(34) & Chr(34) and I want the whole result as a string. The result from your code made the result start with '. Can you tweak your code to make it work?

I understand your requirements. I agree that Chr(34) & Chr(34) is a cleaner conversion of "". However, Chr(34) & "" & Chr(34) gives the same result.

As to the single quote, that is needed to send the string as text. Just copy everything except that and use it for your formula string. When I put this:

=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " & Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" & Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & ")," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "))

... into a cell using

[a3].Formula = "=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " &  Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) &  "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" &  Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & "),"  & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34)  & " " & Chr(34) & "))"

... it produced:

=IF(OR(A4="Yes",A5= "Sure"),"Wired",IF(OR(A9="No",A10="Nah"),"Wireless"," "))

... the same as the input you provided.

ETA: I should point out that you don't have "" anywhere in your formula - instead you have " ", which is correctly represented with Chr(34) & " " & Chr(34).

 

[pingme89]

Thanks. I am trying to create a tool to verify if the Syntax is correct for any specific Formula. I am not trying to copy a formula from one cell to another.

 

[pingme89]

It looks fine but I have a problem trying to test it. I made a button to test the code.

Dim Formula As String


Formula = Worksheets("VBA Tools").Range("H90").Value
Worksheets("Test").Range("B10").Formula = Formula

** Please note I used Range("H90") instead of [A2]. But I get a run-time error.

I understand your requirements. I agree that Chr(34) & Chr(34) is a cleaner conversion of "". However, Chr(34) & "" & Chr(34) gives the same result.

As to the single quote, that is needed to send the string as text. Just copy everything except that and use it for your formula string. When I put this:

=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " & Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" & Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & ")," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & " " & Chr(34) & "))

... into a cell using

[a3].Formula = "=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " &  Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) &  "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" &  Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & "),"  & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34)  & " " & Chr(34) & "))"

... it produced:

=IF(OR(A4="Yes",A5= "Sure"),"Wired",IF(OR(A9="No",A10="Nah"),"Wireless"," "))

... the same as the input you provided.

ETA: I should point out that you don't have "" anywhere in your formula - instead you have " ", which is correctly represented with Chr(34) & " " & Chr(34).

 

[JonXL]

This will give the nicer Chr(34) & Chr(34) for "".

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 = "=SUBSTITUTE(" & [A2].Value & "," & Chr(34) & "Chr" & "(34) " & Chr(38) & " " & Chr(34) & Chr(34) & Chr(34) & Chr(34) & " " & Chr(38) & " Chr" & "(34)" & Chr(34) & "," & Chr(34) & "Chr" & "(34) " & Chr(38) & " Chr" & "(34)" & Chr(34) & ")"
[A2].Value = "'" & [A2].Value
[A1].Value = [A1].Formula



End Sub

As to your runtime error: That is to be expected.

The way you are using it, it is passing the Chr(34) literally to the formula.

The conversion only works, and (as others have pointed out) is only necessary, when you want to work with the text of your formula directly in the VBA editor. If you are just referencing formulas and moving them around, you don't need to convert it to the Chr(34) format.

If you do this:

Worksheets("Test").Range("B2").Formula = "=IF(OR(A4=" & Chr(34) & "Yes" & Chr(34) & ",A5= " & Chr(34) & "Sure" & Chr(34) & ")," & Chr(34) & "Wired" & Chr(34) & ",IF(OR(A9=" & Chr(34) & "No" & Chr(34) & ",A10=" & Chr(34) & "Nah" & Chr(34) & ")," & Chr(34) & "Wireless" & Chr(34) & "," & Chr(34) & Chr(34) & "))"

It will send the formula to B2 that looks like:

=IF(OR(A4="Yes",A5= "Sure"),"Wired",IF(OR(A9="No",A10="Nah"),"Wireless",""))

And excel will be able to evaluate that formula correctly.

 

[pingme89]

Thanks for all your help. It works like a charm. I only made one modification.

[I86].Value = "'" & [I86].Formula
[H90].Value = "=SUBSTITUTE($I$86," & 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) & ")"
[H90].Value = "'" & Chr(34) & [H90].Value
[H86].Value = [H86].Formula

Code = ThisWorkbook.Worksheets("VBA Tools").Range("H90").Value
DataObj.SetText Code
DataObj.PutInClipboard

In this way, I can paste the string directly into the VBE.
I really appreciate this. This was really driving me crazy.

 

[JonXL]

Excellent. I'll be honest, I've been converting these by hand - often spending hours doing so only to find I missed a quote somewhere.

Your thread prompted me to find an automated solution. And I like the addition of putting it into the clipboard directly. ☺

This will save me a lot of time. Thank you for asking this question.