VBA - Call Excel Object in Function from Add-in

1
votes

I have a Excel Object named ThisWorkbook and a Module named Module1. In Module1, I have a function called function1. ThisWorkbook has a private sub called sub1. When user call this function, I want Excel to do sub1 first and if it has no error, perform the rest of the function. However, I am unable to process sub1 when call function1.

Excel Object - ThisWorkbook

Private WithEvents App As Application

Public Sub sub1(some parameters)
...
If (condition) Then
  Msgbox ()
End If
...
End Sub

Private Sub Workbook_Open()
  Set App = Application
End Sub

Excel Module - Module1

Function function1(Add As String, some parameters) As String
    ThisWorkbook.sub1(some parameters)
    ...
End Function

** updated frequently to show the current state of code

2
vbaexcel
ThisWorkbook.sub1 instead of Call sub1. – Vityata
Sub1 needs to be Public, not Private – Rory
Hi @Rory, it has been set to Public (as updated) but still unable to call. – Eddie
If you use parentheses when calling it, you must use Call: Call ThisWorkbook.sub1(some parameters). Otherwise, remove the parentheses: ThisWorkbook.sub1 some, parameters – Rory
Remove the parentheses, it looked like this - App_SheetSelectionChange ByVal Sh As Object, ByVal Target As Range - but it still did not work. Tried the first method of calling, both compiled errors are syntax error. – Eddie

2 Answers

0
votes

Since Sub1 is within the scope of ThisWorkbook and not in a global module you need to specify that in your call:

Call ThisWorkbook.Sub1
0
votes

When you want to refer to a sub, which is in ThisWorkbook, you should refer to the ThisWorkbook as well like this:

ThisWorkbook.sub1 instead of Call sub1.