Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 12
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Help - Can't get excel to calculate properly it''''''''sh hardy Excel Discussion (Misc queries) 1 February 10th 10 03:32 PM
The formula does not calculate properly... Help me if you could.. xokaido Excel Worksheet Functions 4 July 30th 07 04:35 PM
Excel should calculate exponentials properly (right to left) expatrie Excel Worksheet Functions 5 April 26th 06 08:34 AM
Sum formula is not adding up properly Michelle Excel Discussion (Misc queries) 4 November 4th 05 12:18 PM
How to calculate/properly display significant figures ending in 0 A Zaffiro Excel Worksheet Functions 5 June 28th 05 08:36 PM


All times are GMT +1. The time now is 11:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"