Formulas into VBA

I have a spreadsheet with approximately 90 spreadsheets that my boss would
like me to take the formulas out of the cells and put them into VBA code (I
have no idea why). I am somewhat at a loss. Does anybody know of a way to
take a worksheet and move it into VBA without doing it one thing at a time?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200807/1

Rick
Wed Jul 23 16:56:04 CDT 2008

You might want to tell your supervisor that doing what he asked will in all
probability slow down the execution of the spreadsheets dramatically.
Embedded formulas (what you have now) tend to be much faster than calling
out to VB just to have VB insert value back into the spreadsheet. You should
wait for the Excel MVPs to weigh in here first; but, personally, I think
this is a very, very bad idea.

Rick


"lonnierudd via OfficeKB.com" <u11209@uwe> wrote in message
news:879869a797012@uwe...
>I have a spreadsheet with approximately 90 spreadsheets that my boss would
> like me to take the formulas out of the cells and put them into VBA code
> (I
> have no idea why). I am somewhat at a loss. Does anybody know of a way to
> take a worksheet and move it into VBA without doing it one thing at a
> time?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200807/1
>

lonnierudd
Wed Jul 23 18:06:41 CDT 2008

If nothing else it seems like a lot of work for no gain. I'll speak to him
about it, but he isn't likely to tell me why, unless he thinks VBA is magic
and the VERY long and cryptic formulas detracted from what he wanted to see.

Rick Rothstein (MVP - VB) wrote:
>You might want to tell your supervisor that doing what he asked will in all
>probability slow down the execution of the spreadsheets dramatically.
>Embedded formulas (what you have now) tend to be much faster than calling
>out to VB just to have VB insert value back into the spreadsheet. You should
>wait for the Excel MVPs to weigh in here first; but, personally, I think
>this is a very, very bad idea.
>
>Rick
>
>>I have a spreadsheet with approximately 90 spreadsheets that my boss would
>> like me to take the formulas out of the cells and put them into VBA code
>> (I
>> have no idea why). I am somewhat at a loss. Does anybody know of a way to
>> take a worksheet and move it into VBA without doing it one thing at a
>> time?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200807/1

lonnierudd
Wed Jul 23 20:31:08 CDT 2008

It's what he wants, so if there's somebody with an idea, I would be grateful.

lonnierudd wrote:
>If nothing else it seems like a lot of work for no gain. I'll speak to him
>about it, but he isn't likely to tell me why, unless he thinks VBA is magic
>and the VERY long and cryptic formulas detracted from what he wanted to see.
>
>>You might want to tell your supervisor that doing what he asked will in all
>>probability slow down the execution of the spreadsheets dramatically.
>[quoted text clipped - 11 lines]
>>> take a worksheet and move it into VBA without doing it one thing at a
>>> time?

--
Message posted via http://www.officekb.com

Dick
Thu Jul 24 21:21:50 CDT 2008

On Thu, 24 Jul 2008 01:31:08 GMT, "lonnierudd via OfficeKB.com" <u11209@uwe>
wrote:

>It's what he wants, so if there's somebody with an idea, I would be grateful.
>
>lonnierudd wrote:
>>If nothing else it seems like a lot of work for no gain. I'll speak to him
>>about it, but he isn't likely to tell me why, unless he thinks VBA is magic
>>and the VERY long and cryptic formulas detracted from what he wanted to see.
>>
>>>You might want to tell your supervisor that doing what he asked will in all
>>>probability slow down the execution of the spreadsheets dramatically.
>>[quoted text clipped - 11 lines]
>>>> take a worksheet and move it into VBA without doing it one thing at a
>>>> time?

I'd like to go on record as saying this is a bad idea. But it sounds like
you're stuck, so here's something. This is a simple proof of concept macro
and it will need some significant adjustments to apply to your actual
situation.

Sub MakeFormulaCode()

Dim rCell As Range
Dim sResult As String

sResult = "With Workbooks(""MyBook.xls"").Sheets(""Sheet1"")" &
vbNewLine

For Each rCell In Sheet1.UsedRange.Cells
If rCell.HasFormula Then
sResult = sResult & vbTab & _
".Range(""" & rCell.Address & _
""").Value = [" & rCell.Formula & "]" & vbNewLine
End If
Next rCell

sResult = sResult & "End With"

Debug.Print sResult

End Sub

This will generate code that you can paste into a macro. All it does is
record which formulas go in which cells and executes the formula in code and
puts the result in the cell. In addition to additional sheets, you'll have
to figure out how to handle formulas that return errors. Anyway, good luck
and I'm glad I don't have to do this (but I'm still sympathetic).
--
Dick