Custom Functions are static?

[Shannon]

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.

[JE McGimpsey]

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 <[email protected]>,
Shannon <[email protected]> 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.

[Shannon]

Thank you for that --

Most of the benefit I am getting from custom functions at present is
in determining range references in the first place.

I try to automatically reference rows in several sheets derived from
outside CSV daata and sharing only one or two comonalities. The custom
functions save a BUNCH of time because you can logically iterate
through a list of colum figures, rather than generating an offset
range using MATCH and then referencing everything with OFFSET

for me, there are not too many times I have to go through the mental
loops of something like

=sum(offset(sheet1!a:345,offset(sheet3!F47,0,Sheet7!D3),offset(sheet....

blahblah triple and qradruple-nested offsets get horrendously
complicated.

It is so much easier (for me anyway) to be able to detemrine the
ranges I need to pull data from logically and programatically, so I
will try the Volatile thing.

Overhead for me is not a concern if the machine cn tolerate it. I am
using Exces to xtract information from several various outsside
sources. If the worksheet took even an hour to calulate, that woul be
an hour I would not have to spend reading and hand-copying the dat I
need.

Thanks so much for your help and information!

Shannon





On Fri, 19 May 2006 23:01:10 -0600, JE McGimpsey
<[email protected]> wrote:

>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 <[email protected]>,
> Shannon <[email protected]> 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.