You could try using the old Getz/Kaplan Excel 97 AddressOf emulator.
I just tried this with Excel XP (2002) and nothing rigorous. I had problems
at first but it seems to work now
Private Declare Function GetCurrentVbaProject Lib "vba332.dll" _
Alias "EbGetExecutingProj" _
(hProject As Long) As Long
Private Declare Function GetFuncID Lib "vba332.dll" _
Alias "TipGetFunctionId" _
(ByVal hProject As Long, _
ByVal strFunctionName As String, _
ByRef strFunctionID As String) As Long
Private Declare Function GetAddr Lib "vba332.dll" _
Alias "TipGetLpfnOfFunctionId" _
(ByVal hProject As Long, _
ByVal strFunctionID As String, _
ByRef lpfnAddressOf As Long) As Long
Sub testHasUDF()
MsgBox HasUDF(Range("A1"))
MsgBox HasUDF("myUDF)
End Sub
Function HasUDF(R As Range) As Boolean
On Error Resume Next
Dim strFormula As String
strFormula = Mid(R.Formula, 2, Application.WorksheetFunction.Find("(",
R.Formula) - 2)
If AddrOf(strFormula) < 0 Then HasUDF = True
End Function
Public Function AddrOf(CallbackFunctionName As String) As Long
'AddressOf operator emulator for Office97 VBA
'Authors: Ken Getz and Michael Kaplan
Dim aResult As Long
Dim CurrentVBProject As Long
Dim strFunctionID As String
Dim AddressOfFunction As Long
Dim UnicodeFunctionName As String
'convert the name of the function to Unicode system
UnicodeFunctionName = StrConv(CallbackFunctionName, vbUnicode)
'if the current VBProjects exists...
If Not GetCurrentVbaProject(CurrentVBProject) = 0 Then
'...get the function ID of the callback function, based on its
'unicode-converted name, to ensure that it exists
aResult = GetFuncID(hProject:=CurrentVBProject, _
strFunctionName:=UnicodeFunctionName, _
strFunctionID:=strFunctionID)
'if the function exists indeed ...
If aResult = 0 Then
'...get a pointer to the callback function based on
'the strFunctionID argument of the GetFuncID function
aResult = GetAddr(hProject:=CurrentVBProject, _
strFunctionID:=strFunctionID, _
lpfnAddressOf:=AddressOfFunction)
'if we've got the pointer pass it to the result of the function
If aResult = 0 Then
AddrOf = AddressOfFunction
End If
End If
End If
End Function
--
HTH
Bob Phillips
"NickHK" wrote in message
...
RAFAAJ2000,
As I said, if you read the help:
<HELP
AddressOf Operator
A unary operator that causes the address of the procedure it precedes to
be
passed to an API procedure that expects a function pointer at that
position
in the argument list.
</HELP
I'm no expert on this type of thing, but support for AddressOf under VBA
is
different to that under VB.
Also, as you can see, AddressOf does not take a string argument.
NickHK
"RAFAAJ2000" wrote in message
...
Thanks Nickh for the reply.
Basically, I am trying to create a Function that will return TRUE if a
worksheet Cell contains a UDF, FALSE if it doesn't.
The idea is that if the function in the Cell is a UDF then there must be
function procedure somewhere in the current VbProject ( I assume the UDF
is
defined in the current project) which means that the AddressOf Operator
should return the Procedure handle otherwise it should return 0.
Here is the code I have so far which obviously generates a Compile error
if
you try it :
Code:
Function HasUDF(R As Range) As Boolean
On Error Resume Next
Dim strFormula As String
strFormula = Mid(R.Formula, 2,
Application.WorksheetFunction.Find("(",
R.Formula) - 2)
'THIS IS WHERE THE COMPILER ERRORS OUT !!!!!!!!!!!!!
IF ADDRESSOF strFormula < 0 THEN HasUDF=True
End Function
Sub TEST()
MsgBox HasUDF(Range("A1"))
End Sub
Any help with this would be much appreciated.
Regards.
"NickHK" wrote:
RAFAAJ2000,
Did you read the help on AddressOf ?
Where are the functions that you wish to call ?
NickHK
"RAFAAJ2000" wrote in message
...
Hi all,
I am trying to store the name of a procedure in a variable and then
pass
this variable as the argument of the AddressOf operator but it just
doesn't
work !
The addressOf seems to only accept litteral values from its popup
list
of
arguments.
Any idea or workaround to make this work ?
I definitly need to store the procedure name in a variable as part
of
my
code.
Regards.