Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
Hi folks, hope someone can help with this little tear-your-hair-out
number. I have an array formula that I use to calculate row-by-row totals from a table range. It looks like this: =SUM(OFFSET(TableWks,ROW(Wks_Total)-10,0,1,COLUMNS(TableWks))) where TableWks is a multi-column, multi-row table, and Wks_Total is the calculating column itself. There is no problem with the array formula; it does exactly what I want it to do. Except when I run a VBA procedure (what it does is not important, I don't think); the result of the array formula comes up the same in every row, corresponding to the sum of the first column in TableWks, at procedure's end. A simple tap of the F9 key, however, and it corrects itself. I think this corresponds to some sort of array formula/volatility problem as discussed here http://www.decisionmodels.com/calcsecretsj.htm but none of the fixes there worked. Indeed, I've tried every fix I can think of, including: - not setting calculate to Manual at proc's start or restoring it to Automatic at proc's end - liberal use of DoEvents - liberal use of .Calculate and .EnableCalculation - various forms of copying/pasting the .FormulaArray of Wks_Total - various forms of manually setting .FormulaArray at runtime and none of them work. I have tried this in both Excel 2007 and 2003, both running on Windows XP. As I say, simply invoking calculate on the sheet--nothing deeper-- causes the array results to right themselves, as soon as the app state has returned to user control. But something is keeping this formula from working right while under VBA control. Thanks in advance for any thoughts you might have. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
You macro isn't letting the workbook fully update all the calculations. this could be for a number of different reasons. You could be turing off screen updating (or not trun screen updating) off. Are you using a UDF? A UDF for the macro to run porperly must have all the cell used in the function passed as a parameter. A UDF only gets run when a parameter in in the function call get changed. Here is a simply UDF Function Myfunction() Myfunction = Range("A1") end function This function will never get run because the workbook only call functions when the cells in the parameter list get changed. A function should look like this Function Myfunction(Target) Myfunction = Target end function Then call the function like this =MyFunction(A1) -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180681 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
No, there are no in-cell UDFs. (This is much of what the link I
pointed to discusses, and as I already stated, nothing discussed there as a solution has worked for me.) In 13 years of Excel programming, I have never heard of turning screen updating off as a cause for calculation incompleteness. But nevertheless, I have now tried disabling it completely, and can add .ScreenUpdating to the list of workarounds that don't work around. On Feb 19, 1:03*am, joel wrote: You macro isn't letting the workbook fully update all the calculations. *this could be for a number of different reasons. *You could be turing off screen updating (or not trun screen updating) off. * Are you using a UDF? *A UDF for the macro to run porperly must have all the cell used in the function passed as a parameter. *A UDF only gets run when a parameter in in the function call get changed. Here is a simply UDF Function Myfunction() Myfunction = Range("A1") end function This function will never get run because the workbook only call functions when the cells in the parameter list get changed. *A function should look like this Function Myfunction(Target) Myfunction = Target end function Then call the function like this =MyFunction(A1) -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=180681 Microsoft Office Help |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
One more piece of information that may be useful: Excel 2003 (filed
saved as .xls) and 2007 (as .xlsm) do not actually produce the same result as the code call ends. In 2007, I receive #N/A errors, whereas 2003 produces the single-column-sum result described in my original post. A manual calculation call fixes the wks in both versions however. I have also now tried deleting and re-adding range names at runtime. Still no luck. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
Just some more thoughts. Pressing the F9 buton and getting the correct results means that the calculations didn't complete, not that the formula is wrong which is what you are saying. so you have to start thinking why the formula didn't complete. The obvious answers is there some error or the dependencies chain of events is being broken. Lets see if there are errors. A common problem when soembody goes from one version of excel to another on the same PC or even differnt PC is the break option is different. Try changing the following VBA menu Tools - Options - General - Error Trapping. Set the error trapping to "Break on all Errors". Maybe this will give some clues. I can't tell if your formula is giving valid rows and column locations. Maybe you are getting an error becuse your row/column is less than 1, or you column reference is greater than 256. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180681 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
On Thu, 18 Feb 2010 19:42:13 -0800 (PST), downwitch
wrote: Except when I run a VBA procedure (what it does is not important, I don't think); Since it works when you don't run the VBA procedure, and doesn't work when you do run it ... it might be a whole lot easier to find your problem if you post the code. There are so many possibilities .. --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
After exhausting every option in terms of reworking my code and making
sure there was nothing in there that was causing a problem, I decided to reproduce the error in the simplest possible setup, in hopes of getting a little (more) help here. You can now see what I see: create a fresh blank workbook, add a module to it, and paste this code in: '---BEGIN CODE--- Sub Test_Me1() Create_Test Crash_Test End Sub Sub Test_Me2() Create_Test Crash_Test2 End Sub Sub Create_Test() Dim wks As Excel.Worksheet Set wks = ThisWorkbook.Worksheets(1) With wks .Range("$B$2").Value = "'2010" .Range("$C$2").Value = "'2011" .Range("$D$2").Value = "'2012" .Range("$E$2").Value = "'2013" .Range("$G$2").Value = "RowTotal" .Parent.Names.Add Name:="Sheet1!TableWks", RefersTo:="=Sheet1! $B$3:$E$11" .Parent.Names.Add Name:="Sheet1!Wks_Total", RefersTo:="=Sheet1! $G$3:$G$11" .Range("Wks_Total").FormulaArray = _ "=SUM(OFFSET(TableWks,ROW(Wks_Total)-3,0,1,COLUMNS(TableWks)))" End With Set wks = Nothing End Sub Sub Crash_Test() Dim wks As Excel.Worksheet Set wks = ThisWorkbook.Worksheets(1) With wks .Range("TableWks").Value = 0 .Range("$B$4").Value = 31 .Range("$C$5").Value = 12 .Range("$D$3").Value = 9 .Range("$E$5").Value = 15 .Range("$B$6").Value = 121 .Range("$C$6").Value = 19 .Range("$D$7").Value = 6 .Range("$D$8").Value = 222 .Range("$E$9").Value = 43 End With Set wks = Nothing End Sub Sub Crash_Test2() Dim rng As Excel.Range Set rng = ThisWorkbook.Worksheets(1).Range("TableWks") With rng .ClearContents .Value = 0 .Cells(2, 1).Value = 31 .Cells(3, 2).Value = 12 .Cells(4, 3).Value = 9 .Cells(5, 3).Value = 15 .Cells(4, 1).Value = 121 .Cells(5, 2).Value = 19 .Cells(6, 3).Value = 6 .Cells(7, 3).Value = 222 .Cells(8, 4).Value = 43 End With Set rng = Nothing End Sub '---END CODE-- Then all you have to do, from the immediate window, is run Test_Me1 or Test_Me2 (or, if you want, run Create_Test and Crash_Test or Crash_Test2 separately, if you feel like fiddling in between) to see the error result I'm getting. Make one manual data change or hit F9 once user control returns--invoke volatility--and you'll see the error vanish before your very eyes. Note that this occurs without any UDFs at all, and without altering .ScreenUpdating, .Calculation, etc. Any help on what is causing this formula to fail would be really, really appreciated, as I am now into double-digit hours trying to figure this out. If I don't hear back here I will be (cross-)posting this to the worksheet functions forum, as it now appears to straddle VBA and pure Excel. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
On Fri, 19 Feb 2010 20:02:35 -0800 (PST), downwitch
wrote: Then all you have to do, from the immediate window, is run Test_Me1 or Test_Me2 (or, if you want, run Create_Test and Crash_Test or Crash_Test2 separately, if you feel like fiddling in between) to see the error result I'm getting. Make one manual data change or hit F9 once user control returns--invoke volatility--and you'll see the error vanish before your very eyes. Note that this occurs without any UDFs at all, and without altering .ScreenUpdating, .Calculation, etc. Any help on what is causing this formula to fail would be really, really appreciated, as I am now into double-digit hours trying to figure this out. If I don't hear back here I will be (cross-)posting this to the worksheet functions forum, as it now appears to straddle VBA and pure Excel. Good news and bad news. The bad news is that I can't explain why your method doesn't work. It does compute, as you noted, after making any type of manual change in the worksheet. But the Evaluate Formula function only returns a single answer (not an array), so it is hard for me to tease out exactly what is going on with the formula. Given the results, it does seem to return a vertical array of values, but they are inaccessible using the INDEX function. For your example data, I do have a workaround that seems to work. I don't know whether it will be applicable to your original work or not. The work around consists of entering a single array formula in each line, rather than entering the entire Wks_Total as a single array. ================================================== ==== Sub Create_Test() Dim wks As Excel.Worksheet Set wks = ThisWorkbook.Worksheets(1) With wks .Range("$B$2").Value = "'2010" .Range("$C$2").Value = "'2011" .Range("$D$2").Value = "'2012" .Range("$E$2").Value = "'2013" .Range("$G$2").Value = "RowTotal" .Parent.Names.Add Name:= _ "Sheet1!TableWks", RefersTo:="=Sheet1!$B$3:$E$11" .Parent.Names.Add Name:= _ "Sheet1!Wks_Total", RefersTo:="=Sheet1!$G$3:$G$11" .Range("Wks_Total")(1, 1).FormulaArray = _ "=SUM(OFFSET(TableWks,ROW()-ROW(Wks_Total),0,1,COLUMNS(TableWks)))" .Range("Wks_Total").FillDown End With Set wks = Nothing End Sub ============================== Of course, doing it this way, there is no need for the formula to be an array formula. ==================================== Sub Create_Test() Dim wks As Excel.Worksheet Set wks = ThisWorkbook.Worksheets(1) With wks .Range("$B$2").Value = "'2010" .Range("$C$2").Value = "'2011" .Range("$D$2").Value = "'2012" .Range("$E$2").Value = "'2013" .Range("$G$2").Value = "RowTotal" .Parent.Names.Add Name:= _ "Sheet1!TableWks", RefersTo:="=Sheet1!$B$3:$E$11" .Parent.Names.Add Name:= _ "Sheet1!Wks_Total", RefersTo:="=Sheet1!$G$3:$G$11" .Range("Wks_Total")(1, 1).Formula = _ "=SUM(OFFSET(TableWks,ROW()-ROW(Wks_Total),0,1,COLUMNS(TableWks)))" .Range("Wks_Total").FillDown End With Set wks = Nothing End Sub ================================== I'm sorry I couldn't be of more help. Perhaps others can explain things better. --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
I have generated UDF which I called from the worksheet using it like an array formula. It only worked if I selected on the worksheet the exqact size of data I wanted returned from the UDF. It didn't behave like a stadnard function which calculates the size of data that was returned by the UDF and then automatically puts all the data into the worksheet. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180681 Microsoft Office Help |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
Hey, I may not be able to help with the solution but may provide some possibly useful information. In some spreadsheets I have used arrays (Excel 2003), sometimes one or more of the array formulas do not correctly update. Then I need to edit it (crtl-shift-enter) and then the result is correctly displayed. If the same worksheet is used in Excel 2007 then the problem is more serious: most of the array formulas do not update correctly. Ctrl-shift-enter also solves the issue in Excel 2007. This may give a hint on where to look for the solution for your problem (and hopefully for mine also...) downwitch;649148 Wrote: After exhausting every option in terms of reworking my code and making sure there was nothing in there that was causing a problem, I decided to reproduce the error in the simplest possible setup, in hopes of getting a little (more) help here. You can now see what I see: create a fresh blank workbook, add a module to it, and paste this code in: '---BEGIN CODE--- Sub Test_Me1() Create_Test Crash_Test End Sub Sub Test_Me2() Create_Test Crash_Test2 End Sub Sub Create_Test() Dim wks As Excel.Worksheet Set wks = ThisWorkbook.Worksheets(1) With wks .Range("$B$2").Value = "'2010" .Range("$C$2").Value = "'2011" .Range("$D$2").Value = "'2012" .Range("$E$2").Value = "'2013" .Range("$G$2").Value = "RowTotal" .Parent.Names.Add Name:="Sheet1!TableWks", RefersTo:="=Sheet1! $B$3:$E$11" .Parent.Names.Add Name:="Sheet1!Wks_Total", RefersTo:="=Sheet1! $G$3:$G$11" .Range("Wks_Total").FormulaArray = _ "=SUM(OFFSET(TableWks,ROW(Wks_Total)-3,0,1,COLUMNS(TableWks)))" End With Set wks = Nothing End Sub Sub Crash_Test() Dim wks As Excel.Worksheet Set wks = ThisWorkbook.Worksheets(1) With wks .Range("TableWks").Value = 0 .Range("$B$4").Value = 31 .Range("$C$5").Value = 12 .Range("$D$3").Value = 9 .Range("$E$5").Value = 15 .Range("$B$6").Value = 121 .Range("$C$6").Value = 19 .Range("$D$7").Value = 6 .Range("$D$8").Value = 222 .Range("$E$9").Value = 43 End With Set wks = Nothing End Sub Sub Crash_Test2() Dim rng As Excel.Range Set rng = ThisWorkbook.Worksheets(1).Range("TableWks") With rng .ClearContents .Value = 0 .Cells(2, 1).Value = 31 .Cells(3, 2).Value = 12 .Cells(4, 3).Value = 9 .Cells(5, 3).Value = 15 .Cells(4, 1).Value = 121 .Cells(5, 2).Value = 19 .Cells(6, 3).Value = 6 .Cells(7, 3).Value = 222 .Cells(8, 4).Value = 43 End With Set rng = Nothing End Sub '---END CODE-- Then all you have to do, from the immediate window, is run Test_Me1 or Test_Me2 (or, if you want, run Create_Test and Crash_Test or Crash_Test2 separately, if you feel like fiddling in between) to see the error result I'm getting. Make one manual data change or hit F9 once user control returns--invoke volatility--and you'll see the error vanish before your very eyes. Note that this occurs without any UDFs at all, and without altering .ScreenUpdating, .Calculation, etc. Any help on what is causing this formula to fail would be really, really appreciated, as I am now into double-digit hours trying to figure this out. If I don't hear back here I will be (cross-)posting this to the worksheet functions forum, as it now appears to straddle VBA and pure Excel. -- Rmorrone ------------------------------------------------------------------------ Rmorrone's Profile: 1515 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=180681 Microsoft Office Help |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
Thanks for all the feedback everyone.
Ron, yes, there are plenty of row-based workarounds I can use; I was trying to stick to the simplicity of just the one array formula, and the fact that it works so straightforwardly for an Excel user unless that user's name is VBA is maddening. If I understand what you're saying correctly Joel, yes, I think the dynamic nature of the array must be where the problem lies. What I can't believe is that there is no way to force the _exact same_ type of calculation that takes place when a user makes a simple change via the UI (and Rmorrone re-entering a formula counts as such a change--I don't want the user to have to do anything here) to occur through code. Doesn't sound like I will get an explanation for it here though-- I'll try cross-posting, though I don't have much hope for an answer from that either. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula does not calculate correctly when run from macro
On Sun, 21 Feb 2010 18:18:36 -0800 (PST), downwitch
wrote: Thanks for all the feedback everyone. Ron, yes, there are plenty of row-based workarounds I can use; I was trying to stick to the simplicity of just the one array formula, and the fact that it works so straightforwardly for an Excel user unless that user's name is VBA is maddening. If I understand what you're saying correctly Joel, yes, I think the dynamic nature of the array must be where the problem lies. What I can't believe is that there is no way to force the _exact same_ type of calculation that takes place when a user makes a simple change via the UI (and Rmorrone re-entering a formula counts as such a change--I don't want the user to have to do anything here) to occur through code. Doesn't sound like I will get an explanation for it here though-- I'll try cross-posting, though I don't have much hope for an answer from that either. I sure hope you get an answer as to the "why", and that I see the response. There are many more knowledgeable than I around here. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why doesn't this array formula calculate properly using VBA? | Excel Worksheet Functions | |||
functions do not calculate correctly | Excel Worksheet Functions | |||
How come Excel can't calculate any =SIN(x) or =COS(y) correctly? | Excel Discussion (Misc queries) | |||
Excel won't calculate my formulas correctly. | Excel Worksheet Functions | |||
Formula doesn't calculate correctly | Excel Worksheet Functions |