XL can't know to update the function unless you somehow tell it that the
function depends on that range, or you make the function update whenever
the worksheet is calculated.

The latter you accomplish by adding

Application.Volatile

after your Function doSum(x, y) statement. That, however, adds a bunch
of overhead if you use it extensively throughout your workbook.

The other way is to add the range to the argument list:

Public Function doSum(ByVal x As Long, ByVal y As Long, _
ByRef rng As Range) As Double
'...add the 3-d matrix
'...return the total
End Function

and call as

=doSum(1, 3, A1:C3)




In article ,
Shannon wrote:

Hi all,

Thanks in advance for taking the time to read this and help a newbie
to the world of expanded worksheets (beats moving to D and
manipulating in ASP, but you gotta do what your three brain cells will
allow).

Here is my problem:

1 2 3
A 1 1 1
B 1 1 1
C 1 1 1



Super simplified, but that's my tst worksheet.

I have a custom function something along the lines of:

function doSum(x,y)
...add the 3-d matrix
....return the total
end function

So I call doSum(1,3) and get the good answer of 9 somewhere near the
bottom of the sheet.

Now, with a native function, if I changed A-1 to 3, the "9" would
automatically update to 12

That ain't happenin' on my custom functions. To update, I have to
delete and then re-paste all the function calls.

Like I mentioned before, I have three brain cells to dedicate to this
stuff, but have found a gazliion uses for custom functions (text,
offsets, lists, etc) -- but they go from gold to steaming crap if I
have to leave the sheet static or re-do EVERYTHING.

Even if I INSERT a column, the custom functions go to #ERROR rather
than reconfiguring for the inserted column.

Man, my EUREKA hours have gone to seed, and I would sure appreciate
any assistance anyone can provide.

BTW, I am assuming I am poting ot the ocrrect forum, if custom
functions are not considered programming, I apologize for being
off-topic.

thanks again for your time and any information you can provide.