AddressOf Nightmare

[Guest]

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.

 

[NickHK]

RAFAAJ2000,
Did you read the help on AddressOf ?

Where are the functions that you wish to call ?

NickHK

 

[Guest]

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]

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

 

[Bob Phillips]

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

 

[Bob Phillips]

Sorry, ignore that. I just figured out why the problems I initially had
stopped. I tested in XL97 and that worked, it still doesn't work in later
versions. Needs more work, but it does work with 97.

 

[Guest]

Thanks Bob,

Sounds promising...Hope we find a solution for versions later than 97

Regards.

 

[DM Unseen]

If your just looking to see if a certain string is and UDF, ie. a
procedure, why not use the VBAIDE?

you could use something like

Public Function isUDF(strProc As String) As Boolean
Dim module As VBIDE.VBComponent
Dim wb As Workbook
Dim lngLine As Long

isUDF = False

If TypeName(Application.Caller) = "Range" Then

Set wb = Application.Caller.Parent.Parent

For Each module In wb.VBProject.VBComponents

If module.Type = vbext_ct_StdModule Then
lngLine = module.CodeModule.ProcStartLine(strProc,
vbext_pk_Proc)
If lngLine > 0 Then
isUDF = True
Exit Function
End If
End If
End If

End Function

DM Unseen

 

[Guest]

DM Unseen,

Thanks for the code. I actually have a similar code already which also makes
use of the VBIDE Library but I thought using the AddressOf operater would be
cleaner and would not need a loop.

Regards.