Application.FormulaConvert

[Michael Beckinsale]

Hi All,

I am one of those people who have problems with the syntax of complicated
formulas in VBA. Therefore l was hoping to find a simple way of overcoming
the problem. Having input the formula into a cell and using the Record
macro, F2, Enter, method always returns an R1C1 formula in VBA even if the
macro recorder is NOT set to relative.

The TEST code below effectively does what l want but l thought it would be
nice to have the routine in my Personal.xls so that l could easily convert
recorded formulas without having to remember the coding.

The 'FormulaString' coding does not work and returns the formula in the
xlR1C1 format with an additional " at the start and beginning when pasting
"=IF(RC[1]="""",""Empty"",RC[1]" into the InputFormula and Error 2015 when
pasting =IF(RC[1]="""",""Empty"",RC[1])

1) Can anybody tell me what is wrong with the 'FormulaString' code
2) Would it be possible to fully automate the actions ie Record Macro, F2,
Enter etc (possibly using the dreaded SendKeys method)?

TIA

Sub TESTconvertformula()

Dim Result1
Dim IB1

'xxxxx formula created using 'Record Macro' then 'F2' then
'Enter'xxxxxxx
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",""Empty"",RC[1])"
'xxxxx convert formula to xlA1 and apply '$' referencing
xxxxxxxxxxxxxxxx
Result1 = Application.ConvertFormula("=IF(RC[1]="""",""Empty"",RC[1])",
xlR1C1, xlA1, True)
'xxxxx display result
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox (Result1)
'xxxxx display result with quotation marks to copy & paste to VBA code
xxxxxxxxxxxxxxxxxxxx
IB1 = InputBox("The formula you need is:", , """" & Result1 & """")

End Sub

Sub FormulaString()

Dim InputFormula
Dim OutputFormula
Dim Result

InputFormula = InputBox("Please enter xlR1C1 formula to convert to xlA1")
Result = Application.ConvertFormula(InputFormula, xlR1C1, xlA1, True)
OutputFormula = InputBox("Copy and paste this result to your VBA code", ,
Result)

End Sub

Regards

Michael Beckinsale

 

[Tom Ogilvy]

why not enter the formula in a cell then do

msgbox activecell.formula

or demo'd from the immediate window:

? activecell.Formula
=IF(COUNTIF(C2,"*MISC DEBIT~~AMEX*")>0,-D2,IF(COUNTIF(C2,"*MISCELLANEOUS
CREDIT~~AMEX*")>0,D2,0))


It sounds like your worksheet is in A1 mode, so you should get a result in
A1 mode.

 

[Michael Beckinsale]

Tom,

Sorry l obviously did not explain myself clearly (or its me being
particularly thick!)

Both your solutions seem to return the formula in the active cell. What l
want to do is convert the formula to the correct VBA syntax.

1) Say l want to input a formula in cell A1 as follows
=IF(B1="","Empty",B1)
2) In VBA that is written as
"=IF(B1="""",""Empty"",B1)"

I find it difficult to get the syntax correct regarding the extra "s in
complicated formulas so a way round the problem is put the formula in cell
A1, make cell A1 active, Record Macro, F2, Enter. As stated it does not
matter if the code is recorded as relative or absolute. This will produce
the following VBA code with the correct syntax except it is R1C1

3) ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",""Empty"",RC[1])"

Then by replacing the R1C1 referencing with A1 referencing l arrive at the
desired result "=IF(B1="""",""Empty"",B1)"

This relatively quick and easy to do for the above simple formula but very
tedious for more advanced formulas.

Therefore at 3) l have the correct syntax but in R1C1 referencing so l
wanted to convert the referencing to A1 by running the following code and
pasting

"=IF(RC[1]="""",""Empty"",RC[1])" into the InputFormula inputbox.

Sub FormulaString()

Dim InputFormula
Dim OutputFormula
Dim Result
InputFormula = InputBox("Please enter xlR1C1 formula to convert to xlA1")
Result = Application.ConvertFormula(InputFormula, xlR1C1, xlA1, True)
OutputFormula = InputBox("Copy and paste this result to your VBA code", ,
Result)

End Sub

but l get the errors mentioned in my original posting.

Hope l have explained myself better this time.

TIA

why not enter the formula in a cell then do

msgbox activecell.formula

or demo'd from the immediate window:

? activecell.Formula
=IF(COUNTIF(C2,"*MISC DEBIT~~AMEX*")>0,-D2,IF(COUNTIF(C2,"*MISCELLANEOUS
CREDIT~~AMEX*")>0,D2,0))


It sounds like your worksheet is in A1 mode, so you should get a result in
A1 mode.

--
Regards,
Tom Ogilvy

Hi All,

I am one of those people who have problems with the syntax of complicated
formulas in VBA. Therefore l was hoping to find a simple way of
overcoming
the problem. Having input the formula into a cell and using the Record
macro, F2, Enter, method always returns an R1C1 formula in VBA even if
the
macro recorder is NOT set to relative.

The TEST code below effectively does what l want but l thought it would
be
nice to have the routine in my Personal.xls so that l could easily
convert
recorded formulas without having to remember the coding.

The 'FormulaString' coding does not work and returns the formula in the
xlR1C1 format with an additional " at the start and beginning when
pasting
"=IF(RC[1]="""",""Empty"",RC[1]" into the InputFormula and Error 2015
when
pasting =IF(RC[1]="""",""Empty"",RC[1])

1) Can anybody tell me what is wrong with the 'FormulaString' code
2) Would it be possible to fully automate the actions ie Record Macro,
F2,
Enter etc (possibly using the dreaded SendKeys method)?

TIA

Sub TESTconvertformula()

Dim Result1
Dim IB1

'xxxxx formula created using 'Record Macro' then 'F2' then
'Enter'xxxxxxx
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",""Empty"",RC[1])"
'xxxxx convert formula to xlA1 and apply '$' referencing
xxxxxxxxxxxxxxxx
Result1 =
Application.ConvertFormula("=IF(RC[1]="""",""Empty"",RC[1])",
xlR1C1, xlA1, True)
'xxxxx display result
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox (Result1)
'xxxxx display result with quotation marks to copy & paste to VBA
code
xxxxxxxxxxxxxxxxxxxx
IB1 = InputBox("The formula you need is:", , """" & Result1 & """")

End Sub

Sub FormulaString()

Dim InputFormula
Dim OutputFormula
Dim Result

InputFormula = InputBox("Please enter xlR1C1 formula to convert to xlA1")
Result = Application.ConvertFormula(InputFormula, xlR1C1, xlA1, True)
OutputFormula = InputBox("Copy and paste this result to your VBA code", ,
Result)

End Sub

Regards

Michael Beckinsale

 

[Tom Ogilvy]

from help for convertformula:

"Formula Required Variant. A string that containis the formula you want to
convert. This must be a valid formula, and it must begin with an equal sign."

what you want to pass is not a valid formula. So it isn't going to work for
you.

--
Regards,
Tom Ogilvy

Tom,

Sorry l obviously did not explain myself clearly (or its me being
particularly thick!)

Both your solutions seem to return the formula in the active cell. What l
want to do is convert the formula to the correct VBA syntax.

1) Say l want to input a formula in cell A1 as follows
=IF(B1="","Empty",B1)
2) In VBA that is written as
"=IF(B1="""",""Empty"",B1)"

I find it difficult to get the syntax correct regarding the extra "s in
complicated formulas so a way round the problem is put the formula in cell
A1, make cell A1 active, Record Macro, F2, Enter. As stated it does not
matter if the code is recorded as relative or absolute. This will produce
the following VBA code with the correct syntax except it is R1C1

3) ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",""Empty"",RC[1])"

Then by replacing the R1C1 referencing with A1 referencing l arrive at the
desired result "=IF(B1="""",""Empty"",B1)"

This relatively quick and easy to do for the above simple formula but very
tedious for more advanced formulas.

Therefore at 3) l have the correct syntax but in R1C1 referencing so l
wanted to convert the referencing to A1 by running the following code and
pasting

"=IF(RC[1]="""",""Empty"",RC[1])" into the InputFormula inputbox.

Sub FormulaString()

Dim InputFormula
Dim OutputFormula
Dim Result
InputFormula = InputBox("Please enter xlR1C1 formula to convert to xlA1")
Result = Application.ConvertFormula(InputFormula, xlR1C1, xlA1, True)
OutputFormula = InputBox("Copy and paste this result to your VBA code", ,
Result)

End Sub

but l get the errors mentioned in my original posting.

Hope l have explained myself better this time.

TIA

why not enter the formula in a cell then do

msgbox activecell.formula

or demo'd from the immediate window:

? activecell.Formula
=IF(COUNTIF(C2,"*MISC DEBIT~~AMEX*")>0,-D2,IF(COUNTIF(C2,"*MISCELLANEOUS
CREDIT~~AMEX*")>0,D2,0))


It sounds like your worksheet is in A1 mode, so you should get a result in
A1 mode.

--
Regards,
Tom Ogilvy

Hi All,

I am one of those people who have problems with the syntax of complicated
formulas in VBA. Therefore l was hoping to find a simple way of
overcoming
the problem. Having input the formula into a cell and using the Record
macro, F2, Enter, method always returns an R1C1 formula in VBA even if
the
macro recorder is NOT set to relative.

The TEST code below effectively does what l want but l thought it would
be
nice to have the routine in my Personal.xls so that l could easily
convert
recorded formulas without having to remember the coding.

The 'FormulaString' coding does not work and returns the formula in the
xlR1C1 format with an additional " at the start and beginning when
pasting
"=IF(RC[1]="""",""Empty"",RC[1]" into the InputFormula and Error 2015
when
pasting =IF(RC[1]="""",""Empty"",RC[1])

1) Can anybody tell me what is wrong with the 'FormulaString' code
2) Would it be possible to fully automate the actions ie Record Macro,
F2,
Enter etc (possibly using the dreaded SendKeys method)?

TIA

Sub TESTconvertformula()

Dim Result1
Dim IB1

'xxxxx formula created using 'Record Macro' then 'F2' then
'Enter'xxxxxxx
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",""Empty"",RC[1])"
'xxxxx convert formula to xlA1 and apply '$' referencing
xxxxxxxxxxxxxxxx
Result1 =
Application.ConvertFormula("=IF(RC[1]="""",""Empty"",RC[1])",
xlR1C1, xlA1, True)
'xxxxx display result
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox (Result1)
'xxxxx display result with quotation marks to copy & paste to VBA
code
xxxxxxxxxxxxxxxxxxxx
IB1 = InputBox("The formula you need is:", , """" & Result1 & """")

End Sub

Sub FormulaString()

Dim InputFormula
Dim OutputFormula
Dim Result

InputFormula = InputBox("Please enter xlR1C1 formula to convert to xlA1")
Result = Application.ConvertFormula(InputFormula, xlR1C1, xlA1, True)
OutputFormula = InputBox("Copy and paste this result to your VBA code", ,
Result)

End Sub

Regards

Michael Beckinsale

 

[Tim Williams]

Enter the formula into a cell, select it and run something like this

Sub Tester()
Dim clpData As New MSForms.DataObject
Dim sForm As String

sForm = Replace(ActiveCell.Formula, """", """""")
clpData.SetText ("""" & sForm & """")
clpData.PutInClipboard
End Sub

Your formula (along with doubled-up quotes) should be copied to the
clipboard

Tim.

 

[Michael Beckinsale]

Tom / Tim,

Thank you for the feedback. Sorry for the delay in replying.

Tim,
Your code seems to do exactly what l wanted (ie converts a formula in Excel
to the correct syntax in for VBA) and as an extra bonus places it on the
clipboard so that it can be easily inserted into the VBA code.
I had to add MSForms2.0 to the VBE references and have added a message box
to the code to let users know that the information is available on the
clipboard.
I will try it out on some more complex formula's. If it works on those it
will save me a lot of very frustrating time. Many many thanks
Sub FormulaToVBA()

Dim clpData As New MSForms.DataObject
Dim sForm As String

sForm = Replace(ActiveCell.Formula, """", """""")
clpData.SetText ("""" & sForm & """")
clpData.PutInClipboard
MsgBox ("The formula for VBA is : " & """" & sForm & """" & "and has been
placed on the clipboard.")
End Sub


Tom,

I understand what you are saying but this works:
Dim Result1
Result1 = Application.ConvertFormula("=IF(RC[1]="""",""Empty"",RC[1])",
xlR1C1, xlA1, True)

This doesn't:
Sub FormulaString()

Dim InputFormula
Dim OutputFormula
Dim Result

InputFormula = InputBox("Please enter xlR1C1 formula to convert to xlA1")
'the user then pastes in the VBA formula above
Result = Application.ConvertFormula(InputFormula, xlR1C1, xlA1, True)
OutputFormula = InputBox("Copy and paste this result to your VBA code", ,
Result)

End Sub

Why?

Enter the formula into a cell, select it and run something like this

Sub Tester()
Dim clpData As New MSForms.DataObject
Dim sForm As String

sForm = Replace(ActiveCell.Formula, """", """""")
clpData.SetText ("""" & sForm & """")
clpData.PutInClipboard
End Sub

Your formula (along with doubled-up quotes) should be copied to the
clipboard

Tim.

Hi All,

I am one of those people who have problems with the syntax of complicated
formulas in VBA. Therefore l was hoping to find a simple way of
overcoming the problem. Having input the formula into a cell and using
the Record macro, F2, Enter, method always returns an R1C1 formula in
VBA even if the macro recorder is NOT set to relative.

The TEST code below effectively does what l want but l thought it would
be nice to have the routine in my Personal.xls so that l could easily
convert recorded formulas without having to remember the coding.

The 'FormulaString' coding does not work and returns the formula in the
xlR1C1 format with an additional " at the start and beginning when
pasting "=IF(RC[1]="""",""Empty"",RC[1]" into the InputFormula and Error
2015 when pasting =IF(RC[1]="""",""Empty"",RC[1])

1) Can anybody tell me what is wrong with the 'FormulaString' code
2) Would it be possible to fully automate the actions ie Record Macro,
F2, Enter etc (possibly using the dreaded SendKeys method)?

TIA

Sub TESTconvertformula()

Dim Result1
Dim IB1

'xxxxx formula created using 'Record Macro' then 'F2' then
'Enter'xxxxxxx
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",""Empty"",RC[1])"
'xxxxx convert formula to xlA1 and apply '$' referencing
xxxxxxxxxxxxxxxx
Result1 =
Application.ConvertFormula("=IF(RC[1]="""",""Empty"",RC[1])", xlR1C1,
xlA1, True)
'xxxxx display result
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
MsgBox (Result1)
'xxxxx display result with quotation marks to copy & paste to VBA code
xxxxxxxxxxxxxxxxxxxx
IB1 = InputBox("The formula you need is:", , """" & Result1 & """")

End Sub

Sub FormulaString()

Dim InputFormula
Dim OutputFormula
Dim Result

InputFormula = InputBox("Please enter xlR1C1 formula to convert to xlA1")
Result = Application.ConvertFormula(InputFormula, xlR1C1, xlA1, True)
OutputFormula = InputBox("Copy and paste this result to your VBA code", ,
Result)

End Sub

Regards

Michael Beckinsale