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.a...mming/200807/1

Formulas into VBA
 

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.a...mming/200807/1

Formulas into VBA
 

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.a...mming/200807/1

Formulas into VBA
 

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

Formulas into VBA
 

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