AddressOf limitations 2002 vs. 97

[John.Greenan]

Hi,

In Excel 97 there was no inbuilt way to refer to the address of a function.
Kaplan and Getz put togeether a workaround for this (
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=19#19) which is very
useful when required.

Excel 2000 and above have the native AddressOf operator, which offers very
similar functionality with one major difference. AddressOf returns a long,
but only takes as a parameter a function name written in text - no quotes,
just the name. It also will not accept a variable as a parameter.

Kaplan and Getz offered their workaround for Excel 97 with this method
signature
Public Function AddrOf(strFuncName As String) As Long
In this case we can see that we can have a variable function name and the
code figures it the address and returns a long.

So, the Kaplan & Getz code does not work in Excel 2002 and above (the
platform I MUST use). A few hacks around show that the issue is that the
library they reference - vba332.dll is not implemeted in the same way in
vbe6.dll.

So, with the constraints "MUST be in Excel 2002" and "must work with
variable names for the callback address" I find myself stuck. I am working on
a project where I cannor re-engineer to use text for the function names, they
have to be variables and I cannot use another version of Excel.

Can anyone help with this please?

Thanks in advance.

 

[Mike Rosenblum]

Hi John,

I'm not *quite* sure that I understand the problem...

Since the 'AddressOf' operator is available in 2002, why are you adding the
requirement that you have a similar functionality that takes a string
instead?

But given that this is the requirement, I would still think that using late
bound calls either via Execl.Application.Run() or VBA.CallByName() should
also do the trick. I guess late-bound calls are a tad slow, but if this is a
callback, the slight loss of time should not matter. Admittedly it would be
nice to determine the AddressOf from the "name" only once, not each time the
timer fires (as would be implied by repeated calls by VBA.CallByName() or the
like), but I'm not sure that it's worth the effort here?

Using the proper 'AddressOf' operator itself, however, instead of a
home-brewed reflection-based function would seem to be much better though,
yeah? But I guess I don't quite know why you have this set of requirements.

An class interface-based callback would be another natural choice as well,
but, again, I don't know what your needs are.

Just some thoughts! But I don't know if any of this is actually helpful, my
guess is you are aware of all of these options...

-- Mike

 

[Mike Rosenblum]

Hi John,

I'm not *quite* sure that I understand the problem...

Since the 'AddressOf' operator is available in 2002, why are you adding the
requirement that you have a similar functionality that takes a string
instead?

But given that this is the requirement, I would still think that using late
bound calls either via Execl.Application.Run() or VBA.CallByName() should
also do the trick. I guess late-bound calls are a tad slow, but if this is a
callback, the slight loss of time should not matter. Admittedly it would be
nice to determine the AddressOf from the "name" only once, not each time the
timer fires (as would be implied by repeated calls by VBA.CallByName() or the
like), but I'm not sure that it's worth the effort here?

Using the proper 'AddressOf' operator itself, however, instead of a
home-brewed reflection-based function would seem to be much better though,
yeah? But I guess I don't quite know why you have this set of requirements.

An class interface-based callback would be another natural choice as well,
but, again, I don't know what your needs are.

Just some thoughts! But I don't know if any of this is actually helpful, my
guess is you are aware of all of these options...

-- Mike