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.)
0
downwitch
2/22/2010 2:28:10 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

0 Replies
1269 Views

Similar Articles

[PageSpeed] 34

Reply:

Similar Artilces:

"Array Index Out of Bounds"
I recently purchased "Office Professional Ed 2003." With it came "Business Contact Manager." When I double click to open a particular contact or business contact, I get the above "subject" message. Is there a reason, fix, or solution for this? Thanks, John ...

file locked, in use by another
Sorry I posted this earlier in the wrong thread I have an excel file from my computer that is locked I am not sharing the file with anyone else but somehow it's in use and I cannot update it How do I free up the file? Please help I made a copy but now I have message about the macro referencing the old file I open every macro link and specified 'this workbook' for every link. It did not help thanks Nick L. using Office 2000 professional edition ...

Use this critical update which came from the Microsoft Corp.
--mjgyiahricrg Content-Type: multipart/related; boundary="gvoglqml"; type="multipart/alternative" --gvoglqml Content-Type: multipart/alternative; boundary="luvcfojxz" --luvcfojxz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "November 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to continue keeping your computer secure. This update includes the funct...

formula Memory
Hello, Those who like to store complex formulae for future use, can install my Add-In: 'Formula Memory'. This Add-In adds a command to the Help menu, allowing to save or reenter those formulas by just a few clicks of the mouse. Also check my sheets selector. http://users.skynet.be/onderland/Excel.htm 18- SheetsSelector 19- FormuleMemory Luc ...

no formula calculation
In the worksheet no formulas are calculated anymore. When I put the following in a cell: =A1+B1 This text is displayed, no calculation is made. Even when I put the following in, =1+3 no calculation is made. Everything is displayed as is. What is the problem?? Is the cell formatted as General (or some number)? If it's formatted as Text, change it to General, then select the cell and hit F2. Then Enter. You don't have a space character before the leading equal sign? And you don't have tools|options|view tab|formulas checked, do you? Junior Trimon wrote: > > In th...

Nesting formulas
How do I nest these formulas into one cell: =IF(MID(D6,4,1)="-", IF(M6="J",TEXT(0,"0000000"),TEXT("0"&LEFT(D6,2)&MID (D6,4,4),"0000000") IF(M6="G","XXXXXX","yyyyy") IF(M6="U",TEXT(0,"0000000"),TEXT(0,"0000000") ...

using macro to save a file produces runtime error 4198
I upgraded from Win-XP/Office 2003 to Win-7/Office 2007; the code below ran fine in Word 2003 but in Word 2007 generates runtime error 4198. The variables and directory location are valid. The parameters for FileName are updated for Word 2007; the grammer for the FileName appears correct (no grammer errors when macro runs as is, but grammer errors if I change the string.) When I substitute an actual file name in place of the variables, the code runs w/o error, but the Debug.Print statements generate correct strings. I feel kind of dumb- but what is different about use of variab...

additem to combobox with an array
hi, i'm trying to add itemns to an multicolumn combobox (3 columns) using an array. if i just try to add 1 column there is no problems, but when i try t add more columns to the combobox it wont work anymore. tips anyone? tia jock -- jock ----------------------------------------------------------------------- jocke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=76 View this thread: http://www.excelforum.com/showthread.php?threadid=47167 Did you change the .columncount property? I made a small userform and picked up some values off a worksheet to create...

Formula to calculate interest only on a short term loan?
I am not familiar with Excel 2007 and need to calculate a bridge loan MONTHLY INTEREST ONLY scenario, with a fixed %rate, for a range of 1 -6 months. Not sure if if interest accrues daily or monthly. Assuming you have an annual interest rate, a month's interest is: =Principal*IntRate/12 This may be good enough for your purposes. Most financial institutions would calculate the interest based on the number of days in the month, so something like: =Principal*IntRate/365*day(eomonth(a1,0)) Regards, Fred. "Excel2007Help" <Excel2007Help@discussions.microsoft.c...

Need a FORMULA to round off
I have a question, quite simple but complicated for a beginner like me. Say I have a figure .... 16 in CELL A1 and in C1 5.05263158 C2 6.73684211 C3 4.21052632 but wanted the numbers to be rounded to the nearest Hundredth totaling it to be the number A1? How can I do that? I mean if I just do the math it should look like this C1 5.05 C2 6.75 C3 4.20 Please help and thank you in advance. Hi try =ROUND(value,2) -- Regards Frank Kabel Frankfurt, Germany Koji wrote: > I have a question, quite simple but complicated for a > beginner like me. > > Say I have a figure .... ...

Radar Plot using geographical coordinates #2
I am trying to generate a circular radar or XY scatter point plot using geographical coordinates referenced to a tower in the aproximate center (with its own geographical coordinates and the plotted data in 4 quadrants (+X/+Y, -X/+Y, -X/-Y and +X/-Y) in order to plot the RF field strength of an FM broadcast station. I have 5 columns of data: 1 the Latitude coordinates of the tower for center reference. 2 the Longitude coordinates of the tower for center reference. 3 the Latitude coordinates where I took the measurements. 4 the Longitude coordinates where I took the measurements. 5 The RF...

Array+Array
Hi, I have 2 (or more) arrays, how can i combine them into 1? example: Array 1 = "1, 2, 3, 4" Array 2 = "A, B, C, D, E, F" Result: Array_Combined = "1, 2, 3, 4, A, B, C, D, E, F" br, Danny On Jan 7, 9:23=A0am, Danny <dannypct...@gmail.com> wrote: > Hi, > > I have 2 (or more) arrays, how can i combine them into 1? > > example: > Array 1 =3D "1, 2, 3, 4" > Array 2 =3D "A, B, C, D, E, F" > > Result: > Array_Combined =3D "1, 2, 3, 4, A, B, C, D, E, F" > > br, >...

array
Dear programmer I don't understand the array can any one give very easy example to understand the types of array ( I can use command button and text box) Example 'Don't forget to write notes for me ' the next line will define the array. Dim a(2) As String ' the next lines define the data of array a(0) = "1" a(1) = "2" a(2) = "3" And so on Notes: The array is very difficult please give me easy example "a" <A@a.com> wrote in message news:uy9smdVUIHA.4476@TK2MSFTNGP06.phx.gbl... > Dear programmer I don't understa...

how do I create an array
I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3:G11}),""))) please help ...

Force exchange to use my default display name
Hi guys, Is there a way to force Exchange to chande the display name ??? Here's a exemple: If our web site send a email to someone by relaying on the exchange server. With the following information Sender name: "Sales" Sender email: sales@paradoxusa.com To: Customer email adress CC: Allan Tox When I receive the copy of the email I want to see my display name of sales@paradoxusa.com wich is Customer Service Paradox Security and not from Sales... Can you guys have a idea where I have to do my change on the Exchange server ? Thank you...

Excel File in Use Notification Missing??
We are using Windows Server 2000 & several versions of Excel (2000 / XP / 2003). When opening Excel files from the network, SOME users get the Notification that the File is In Use - locked for editing by <user> - with a choice to Open as Read-Only, Notify when avail, or Cancel. The pblm is that NOT EVERYBODY gets this msg - some simply have the file open up with the word (Sharing) in the Title Bar!! When you've got a deskful of work to input you're not always looking at the Title Bar to see if the file is Shared or not. I first thought this was caused by diff vers o...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

Outlook 2003
I've been looking through the newsgroup and see that many people have problems with rules, but I've been having a hard time finding the solution to mine. I have emails coming in from a specific mail account. I have rules to assign some of those email to specific categories, depending on who the sender is. THEN, I have a rules to move emails from that specific account to a specific folder EXCEPT if assigned to this and that category. And FINALLY I have rules that move emails to specific folders, depending on which category they are assigned to. So the order is: - Assign categories dep...

Accessing the CCD array of the camera using MFC
Hello, I am capturing the images from the camera using VC++ MFC . And each image is of size 1280*1024. When I want to do processing over certain area, actually I want to divide the Image height(1024) into 4 blocks (256,256,256,256) keeping the width (1280) constant. So that I will get the whole image into 4 small images. Can anyone please kindly tell me How can I able to do this ? I am thinking of accessing CCD array, If accessing CCD array is a solution for this.. Please help Thanks in advance James First, you need to have a device driver that connects to the camera. This is not an MFC ...

Dragging/ Auto Calculation Problem
Hi all- Issue: I am dragging down rows to auto calculate Problem: It increments in fours Is there a feature that turns this off? Specifics: Sheet2 is four rows of calculations linked from data in on line on Sheet1. (Recorded a Macro to capture this action) So Row 2, Sheet1 is the origin of all my entries on Rows 1,2,3,4 o Sheet2 And Row 3, Sheet1 will be the origin of all my entries on Row 5,6,7,8........Row 4, Sheet1 will be the origin of entries on Row 9,10,11,12 on Sheet2 etc What is Occurring: When I drag boxes on sheet2 to auto complete i starts with Row 6, Sheet1 as the refe...

How can I use SY01400 for checking passwords?
I am currently working on a separate .NET app that allows users to edit some custom tables that i've created. When they first start this app, they are greeted with a simple dialog that asks for a company, plus their username and password. i would like to compare the password they enter to the encrtyped password in dynamics.sy01400, but I'm not sure what kind of encryption that table uses. is it a SQL based encryption, or does GP/Dynamics have their own? thanks! you cannot do that directly. However, if the user is logged in to GP, you can get the password using RetrieveGlobals.dll...

Date formulas
Hi, I'm doing a timesheet. The month begin on the 21 and end on 20th of each month (don't ask). It'easy to set a listmenu to choose the month, have the first day to start with the 21, having 31 line with =1+precedente line. But for the three last line, idon't know how to have it to stop displaying dates after the 20th, for Febuary and the other 30 days months. An idea anyone? thank In A2: =IF(A1="","",IF(MONTH(A1+1)=MONTH(A1),A1+1,"")) Copy down as far as needed -- Kind regards, Niek Otten Microsoft MVP - Excel &q...

can i use hotmail in office outlook? how? what email address i ca.
can i use office outlook2003 to download hotmail email? what email address does outlook2003 support? Hotmail access for new users requires that you use their paid service. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, snow asked: | can i use office outlook2003 to download hotmail email? what email | address does outlook2003 support? If you already own a free hotmail account and would li...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

Array saving question (lbound)
Hello! I am saving an array to a file and then load it again. Open sPath For Binary As #iFile1 If uLoad Then Get #iFile1, , uArray Else Put #iFile1, , uArray End If My array looks like this: Redim myArr(1 to 3) as long myArr(1) = 100 myArr(2) = 500 myArr(3) = 10000 When I load it again, I say: Redim MyNewArr(1 to 3) and call the above sub. For some reason, MyNewArr now looks like this: MyNewArr(0) = 100 MyNewArr(1) = 500 MyNewArr(2) = 3 When I try to access MyNewArr(3) an out of bounds error is thrown. Can somebo...