CODEWITHSUNDEEP
excelvba
Ting Fung Li
Ting Fung Li

Reputation: 46

Defining object part in callbyname function with variables

I am facing a problem when I want to rewrite part of my program. This part is put in module1 and look like as follows:

Public Function InputCorr(Target As MSForms.Control) As Boolean

    If FrmAddRecord1Shown Then
        Target.Value = CallByName(frmAddRecord1, Target.Name & "_Min", VbMethod)
    ElseIf FrmAddRecord2Shown Then
        Target.Value = CallByName(frmAddRecord2, Target.Name & "_Min", VbMethod)
    End If

End Function

And I am going to rewrite like this:

Public Function InputCorr(Target As MSForms.Control) As Boolean

    Dim UF As UserForm

    If FrmAddRecord1Shown Then
        set UF = frmAddRecord1
    ElseIf FrmAddRecord2Shown Then
        set UF = frmAddRecord2
    EndIf 

    Target.Value = CallByName(UF, Target.Name & "_Min", VbMethod)

End Function

FrmAddRecord1Shown and FrmAddRecord2Shown are boolean, indicating which userform (frmAddRecord1 or frmAddRecord2)is active.

However, "run-time error"438": Object doesn't spport this property or method" occurs. I believe I was stuck at the "UF" defining part. Is it possible for me to define the "object" part of "callbyname" function with variable? Please advice. Thank you very much for all yours sincere help.

Upvotes: 0

Views: 3474

Answers (1)

PaulDragoonM
PaulDragoonM

Reputation: 56

CallByName(object, procname, calltype, [args()]) (Reference: https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/callbyname-function) performs an action on the object during run-time (late binding). Before using CallByName , your should make sure that the equivalent early binding operation works.

Depending on the calltype you use in CallByName, the procname corresponds to different members of the object: (The following cases are expressed in the form of "late-binding corresponds to early-binding")

(1) CallByName(object, procname, calltype:=vbGet) corresponds to object.[Property]

(2) CallByName(object, procname, calltype:=vbLet) corresponds to object.[Property, which is a value]

(3) CallByName(object, procname, calltype:=vbMethod) corresponds to object.[Method]

(4) CallByName(object, procname, calltype:=vbSet) corresponds to object.[Property, which is an object]

Now, in your function, you are performing on the UF object (an UserForm object) with CallByName(UF, procname, VbMethod), which corresponds to Case (3) mentioned above. Therefore, your procname should be a Method of the UserForm object. You can check the Methods of the UserForm object in the Object Browser (press F2 in the VBA editor, and find UserForm under the MSForms Libary). The Methods are listed in the following code: (DO NOT run the code, the codes do not do anything.)

Private Sub UserFormMethods()
    Dim ufForm As UserForm

    Set ufForm = frmAddRecord1

    With ufForm
        .Copy
        .Cut
        .Paste
        .RedoAction
        .Repaint
        .Scroll
        .SetDefaultTabOrder
        .UndoAction
    End With
End Sub

However, in your CallByName function, your procname argument is Target.Name. Target in your definition is an MSForms.Control object, which is an object PROPERTY, not a METHOD, of the UserForm object. Therefore it triggers the "run-time error"438": Object doesn't spport this property or method" error.

I think what you are trying to do is actually using CallByName on the Target object (an MSForms.Control object). I would suggest changing your function to:

Public Function InputCorr(Target As MSForms.Control) As Boolean
    Dim UF As UserForm
    Dim ctlTarget As MSForms.Control

    If FrmAddRecord1Shown Then
        Set UF = frmAddRecord1
    ElseIf FrmAddRecord2Shown Then
        Set UF = frmAddRecord2
    End If

    Set ctlTarget = CallByName(UF, Target.Name, VbGet)
    Call CallByName(ctlTarget, "_Min", VbMethod)
End Function

I was not able to find the MSForms.Control._Min method in the object browser. Maybe it is a hidden method but you must make sure this method exists.

Also, you did not specify what your function's return value is. It should have a final line like: InputCorr = ...

Upvotes: 2

Related Questions