1

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

Eddie
  • 43
  • 9
  • `ThisWorkbook.sub1` instead of `Call sub1`. – Vityata May 17 '18 at 09:14
  • `Sub1` needs to be `Public`, not `Private` – Rory May 17 '18 at 09:31
  • Hi @Rory, it has been set to Public (as updated) but still unable to call. – Eddie May 17 '18 at 09:35
  • 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 May 17 '18 at 10:13
  • 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 May 18 '18 at 02:32

2 Answers2

0

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
0

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Hi, does private sub affects the call? I tried both private and public sub sub1 but they gives me compiled error - syntax error. – Eddie May 17 '18 at 09:20
  • @Eddie - hi, it does not. `Call` is a bit old way for programming in VBA, and it is a good idea to avoid it, if you can - https://stackoverflow.com/questions/479891/what-does-the-call-keyword-do-in-vb6 It can cause you some troubles, if you are using arguments, due to the way it refers to them. – Vityata May 17 '18 at 09:22
  • Hi @Vityata, I removed Call but it still did not work - syntax error. This is the sub I want to call ThisWorkbook.App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range). I have put both "ByVal Sh As Object, ByVal Target As Range" in my function input parameters. – Eddie May 17 '18 at 09:26
  • @Eddie - if you remove the call, then the parameters should be passed without parenthesis. – Vityata May 17 '18 at 09:30
  • I have removed the parenthesis - ThisWorkbook.App_SheetSelectionChange ByVal Sh As Object, ByVal Target As Range, the error is syntax error. When I remove parameter - ThisWorkbook.App_SheetSelectionChange and the error is "Argument not optional". – Eddie May 17 '18 at 09:37
  • @Eddie - remove the parenthesis when you call the function, not when you build it. – Vityata May 17 '18 at 09:42
  • Sorry, what I meant is the parenthesis is removed when call function. The sub1 still has parenthesis and parameters. – Eddie May 17 '18 at 09:49
  • @Eddie - so your question should be actually, "How to fire the built-in `SheetSelectionChange` event in VBA?" – Vityata May 17 '18 at 09:55
  • I am not sure, what I intend to do is to call the SheetSelectionChange under Excel Object in Add-in to a function under Module in Add-in; to do SheetSelectionChange first, if no msgbox then do the rest of the function. – Eddie May 18 '18 at 02:27