Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting # of Formulas in a column with formulas and entered data | Excel Worksheet Functions | |||
convert all formulas on a worksheet to aray formulas | Excel Programming | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Formulas not evaluated, Formulas treated as strings | Excel Discussion (Misc queries) | |||
AdvancedFilter on cells with formulas, returning values and not formulas | Excel Programming |