Passing an OBJECT argument to the OnAction Property doesn't work

[Jaafar Tribak]

Any ideas/Workarounds ?

 

[RoryA]

The only ways I know of are to assign the object to a variable that the OnAction routine can access, or pass the name of the object in a way that the OnAction expects (i.e. it needs to know the type of object, or you could pass that too)

 

[Jaafar Tribak]

The only ways I know of are to assign the object to a variable that the OnAction routine can access, or pass the name of the object in a way that the OnAction expects (i.e. it needs to know the type of object, or you could pass that too)

Thanks Rorya.

Passing the exact type of object doesn't work. I've tested it already.The arguments will accept all var types except Objects of any kind.

Fortunately,a quick test by passing the ObjPtr of the Object which is always a Long Type worked flawlessly !

Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
pDest As Any, pSrc As Any, ByVal ByteLen As Long)


Sub test()

    Dim lPtr As Long
    
    lPtr = ObjPtr(Application)
    
    ActiveSheet.Shapes(1).OnAction = "'Macro " & lPtr & "'"


End Sub

Sub Macro(ByVal Ptr As Long)

    Dim oTempObj As Object
    
    CopyMemory oTempObj, Ptr, 4
    MsgBox oTempObj.Name
    CopyMemory oTempObj, 0&, 4

End Sub

 

[RoryA]

I actually meant passing the type name, not an object type (the routine would then need to react accordingly), but as usual you have a more elegant solution.

 

[Jaafar Tribak]

I actually meant passing the type name, not an object type (the routine would then need to react accordingly), but as usual you have a more elegant solution.

Thanks.

You meant something like this :

Sub test()

    Dim sAppName As String
    
    sAppName = Application.Name
    
    
    ActiveSheet.Shapes(1).OnAction = "'Macro " & Chr(34) & sAppName & Chr(34) & "'"


End Sub

Sub Macro(ByVal Arg As String)

    MsgBox (Arg)

End Sub

 

[RoryA]

More like:

 ActiveSheet.Shapes(1).OnAction = "'Macro " & Chr(34) & sAppName & Chr(34) & "," & Chr(34) & "Application" & Chr(34) & "'"

and then the routine has to react based on the 'Application' type passed to it.

 

[Jaafar Tribak]

More like:

 ActiveSheet.Shapes(1).OnAction = "'Macro " & Chr(34) & sAppName & Chr(34) & "," & Chr(34) & "Application" & Chr(34) & "'"

and then the routine has to react based on the 'Application' type passed to it.

Ok , I think I know what you mean - something along these lines :

Sub test()

    Dim sObjectType As String
    
    sObjectType = "Application"
    
    
    ActiveSheet.Shapes(1).OnAction = "'Macro " & Chr(34) & sObjectType & Chr(34) & "'"


End Sub

Sub Macro(ByVal Arg As String)

    Dim Obj1 As Application
    Dim Obj2 As Workbook
    Dim Obj3 As Worksheet

    Set Obj1 = Application
    Set Obj2 = ThisWorkbook
    Set Obj3 = ActiveSheet

    Select Case Arg
    
    Case Is = TypeName(Obj1)
    
        MsgBox Application.Name
    
    Case Is = TypeName(Obj2)
    
        MsgBox ThisWorkbook.Name
    
    Case Is = TypeName(Obj3)
    
        MsgBox ActiveSheet.Name
    
    End Select
  
End Sub

And if we are dealing with Objects of the same Type then we could do the same by checking one of their Properties such as their respective Name.

Gladly, now passing the Object Pointer saves us from all this extra code specially if we are dealing with a large number of Objects of the same Type.

 

[RoryA]

Yep!
As a matter of interest, what are you using it for? I'd never really looked into this as I've never really needed to pass an object from a button before.

 

[Jaafar Tribak]

Yep!
As a matter of interest, what are you using it for? I'd never really looked into this as I've never really needed to pass an object from a button before.

Mainly ou of curiosity as I could see the potential for reducing code editing. I know one could use the Application.Caller Function and then walk the Objet tree up via the Parent Property to access different other objects but I thougth passing the exact Object would be much more flexible specially when a Custom Class instance is the passed argument.

We souldn't also forget that this can equally be used with .Ontime, .OnKey , .OnUndo etc...

 

[RoryA]

Yes - OnTime I think I can see a few uses for it. I should have thought of it really as it's the same basic method I use for resetting the RibbonUI variable.