Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Why doesn't this array formula calculate properly using VBA?
I'm using an array formula to calculate row totals off a table of
values. The formula is elegant and straightforward (adapted from something I found on Chip Pearson's great site), but when it's invoked via VBA it returns incorrect results; any subsequent change via the UI or a simple touch of the F9 key and it corrects itself. I have only a vague grasp of what the problem may be here. What I'm hoping for is (1) a clear explanation of the failure point(s) in the logic with regard to VBA's botching of the calculation, and (2) an alternate array formula that will calculate row totals correctly under VBA. A lot to ask, I know. Code to reproduce the problem: Add to a public module in a new workbook '---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-- Test_Me1 or Test_Me2 will show you the error calculation; a subsequent manual change to the worksheet will correct it. Thanks in advance for any light you can shed. I've put in a lot of time on this, and any more time you can save me would be much appreciated. (This is something of a cross-post from public.excel.programming, so my apologies to those reading this twice.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help - Can't get excel to calculate properly | Excel Discussion (Misc queries) | |||
The formula does not calculate properly... Help me if you could.. | Excel Worksheet Functions | |||
Excel should calculate exponentials properly (right to left) | Excel Worksheet Functions | |||
Sum formula is not adding up properly | Excel Discussion (Misc queries) | |||
How to calculate/properly display significant figures ending in 0 | Excel Worksheet Functions |