CODEWITHSUNDEEP
vbaexcel
Brad
Brad

Reputation: 1480

Calling a Sub within a Form

I currently have a Form set in place within my workbook. This form contains a button. On the click of the button, I would like to call a Sub which is located within the "ThisWorkbook" section. How could I go along of doing this?

Button within form...

Sub CommandButton1_Click()
    Call Main("Test")
End Sub

The Sub that needs to be called within "ThisWorkbook"

Sub Main(DPass As String)
    msgbox Dpass
End Sub

This will give me a compile error of: Sub or Function not defined. Why does this happen?

Upvotes: 3

Views: 8187

Answers (2)

pokemon_Man
pokemon_Man

Reputation: 902

Put your sub in a module and not in your ThisWorkbook unless you have to for some reason, if so use ThisWorkbook.Main "string".

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

There are essentially two types of modules:

  • "Standard/Procedural" modules
  • Class modules

"Document" modules (e.g. ThisWorkbook, Sheet1, etc.) are just special kinds of class modules. Same for "UserForm" modules, which are basically classes with a default instance and a designer.

Members of a class module don't exist at run-time; a class is nothing but a blueprint for an object - so you need to either create an object of that type (the class determines the type), or use an existing one.

ThisWorkbook is an instance of the Workbook class; Sheet1 is an instance of the Worksheet class. Chart1 is an instance of the Chart class; UserForm1 is an instance of the UserForm class. And so on.

If you make a new class module and call it Class1, and add a public procedure to it:

Public Sub DoSomething()
    MsgBox "Something!"
End Sub

Then in order to call DoSomething you need an instance of Class1:

Dim foo As Class1
Set foo = New Class1
foo.DoSomething

If DoSomething is in the ThisWorkbook module, then you can call it by qualifying the method name with the object it exists on, as was mentioned in the comments and in the other answer:

ThisWorkbook.DoSomething

If DoSomething is implemented in a standard/procedural module, then there is no object, the procedure exists in global scope, and you can just do this:

DoSomething

However public members of procedural modules are also exposed as macros (Public Sub) and user-defined functions (Public Function), which you may not want to do.

If you need a procedural module with public members that you can only call from VBA code (and not by clicking a button on a worksheet, or by entering a formula in a cell), then you can specify Option Private Module at the top:

Option Private Module

Public Sub DoSomething()
    ' DoSomething is not exposed as a macro, 
    ' but can be called from anywhere in the VBA project.
End Sub

Upvotes: 6

Related Questions