Excel VBA calling a Function in MSForm from another Function in same MSForm using Run, give error 1004

I have a MSForm with many TextBox fields. I want them to have correct format and correct content so all communication to them goes through getters and setters, preNamed with TBfieldname

Suddenly there occured a situation where data read into the textbox had wrong format and content. I do not want to change my whole structure based on this odd situation, but just call a general function, and if the data is wrong, correct directly to the getters and setters.

  'Initialize it 
    'Add Leader
    If Not CheckForInconsistData("Leader", accounts.Cells(1, 1).Value, _
           accounts.Cells(1, 2).Value, accounts.Cells(1, 3).Value, accounts.Cells(1, 4).Value) Then
      TbLeader = accounts.Cells(1, 1).Value
      ...

Private Function CheckForInconsistData(sCtrlName As String, vCtrl1 As Variant, vCtrl2 As Variant, vCtrl3 As Variant, vCtrl4 As Variant) As Boolean
  If vCtrl1 = "-" And (Not vCtrl2 = "-" Or Not vCtrl3 = "-" Or Not vCtrl4 = "-") Then
    'Clear the fields for member and set it status to changed
    Call SearchAndFillInBoardMember(sCtrlName, 0) 'Member 0 means clear the fields
    Dim sFunctionName As String
    sFunctionName = "UF_TableBoard.HasInconsistTb" & sCtrlName
    Run (sFunctionName, True)
    Run Application.ActiveWorkbook.Name & "!" & sFunctionName, True
  End If
End Function

'Textbox TbLeader, representing a Variant
Private Property Get TbLeader() As Variant
  TbLeader = txtBoxes(bwiTBLeader).GetValue
End Property
Private Property Let TbLeader(ByVal lNewValue As Variant)
  txtBoxes(bwiTBLeader).SetReference lNewValue
  txtBoxes(bwiTBLeader).SetValue lNewValue
End Property
Public Function HasInconsistTbLeader(ByVal bInconsist As Boolean)
  txtBoxes(bwiTBLeader).InconsistData (bInconsist)
End Function

This function say to the userform that the field has changed even the user had not made any changes in GUI

In my general function I test if inputdata is valid, if so call the correct setter dependent on the name of the textbox. Build up a string ‘FunctionName’ representing the setter ” and then using: Run (FunctionName, parameters)
But when running the function through Run, i got error 1004.
I searched in the database on this issue, but did not found any good solution to the problem
My workbook has of course enabled Macroes, and the functions are public. One of the answers i fount to solve such problems was to put it in a module.
The functions is in a MSForm, i belive I could do the same in a MsForm
Tried:

    Dim sFunctionName As String
    sFunctionName = "UF_TableBoard.Tb" & sCtrlName
    Run (sFunctionName, True)
    Run Application.ActiveWorkbook.Name & "!" & sFunctionName, True

But none of them worked.
Is it problem with the notation or is it something compleatly different?
Is there a better way to think through the solution in general?
Thanks for any help that will solve my problem
Jo-Helge

Tried several ways to use the Run function

You did not answer my clarification question, so I will assume that you really need to call a function and use its return…

Having such a function in a user form (UserForm4) (It needs to NOT be Private!):

Public Function Multiplication(x As Long, y As Double) As Double
  Multiplication = x * y
End Function

you can simple call it (from the same workbook) in the next way:

Sub testCallFunctionFromForm() 'it works.
   Dim x As Double
   x = UserForm4.Multiplication(3, 567.47)
   Debug.Print x
End Sub

It will return 1702.41

You can call a function from a different workbookk only if it exists in a standard module. So, you need a function wrapper in such a module, respectively:

Function UFMultiplicationWrapper(a As Long, x As Double) As Double
   UFMultiplicationWrapper = UserForm4.Multiplication(a, x)
End Function

Then activate the above used workbook and call the user form function trough the wrapper function in the next way:

Sub testCallFunctionFromFormOtherWb() 'it works...
   Dim x As Double
   Dim wb As Workbook: Set wb = ActiveWorkbook
   x = Application.Run("'" & wb.Name & "'!" & "UFMultiplicationWrapper", 3, 867.47)
   Debug.Print x
End Sub

It will return 2602.41…

Note: If it is not a matter of ActiveWorkbook, using wb.FullName will also work even if the workbook is not open (it opens it…)

So, in order to receive the returned function output the used parenthesis evaluates the run function output. And placing the workbook name (full name) between “‘” … “‘” takes care of spaces between words contained in the workbook name/full name. Otherwise, it will error, the string parameters being split according to existing spaces…

But, if you need to call a Sub from a user form (the same UserForm4 example) you need such a sub in its code module:

Public Sub MyMessage(strMess As String)
   MsgBox strMess
End Sub

and a wrapper Sub as:

Sub UFSubMessageWrapper(x As String)
  UserForm4.MyMessage x
End Sub

in the same workbook with the respective UserForm.

Then, activate the workbook call the sub it as:

Sub callSubFromOtherWBUserForm()
   Dim wb As Workbook: Set wb = ActiveWorkbook
   Application.Run "'" & wb.Name & "'!" & "UFSubMessageWrapper", "Hello!"
End Sub

It will raise a message containing “Hello!”…

As you can see, no need of parenthesis because nothing is to be evaluated…

Filed under: Kiến thức lập trình - @ 07:53

Thẻ: excelvbaforms

Thiết kế website giá rẻ

Danh mục

Excel VBA calling a Function in MSForm from another Function in same MSForm using Run, give error 1004