CODEWITHSUNDEEP
excelvba
D.W.
D.W.

Reputation: 33

Excel VBA Error: Compile Error: Sub or Function not defined. Calling a Sub in another sheet/module

When the Quote_Button_EmailToBeth() Sub is run, it throws the error"

Compile error: Sub or Function not defined"

and highlights the line that calls the Sub named "EmailToBeth_Unprotect".

As far as I understand, it is actually defined, but in another sheet module:

enter image description here

I originally thought it might be due to the underscore in the Sub name, but removing them doesn't change the error. I also know some people will scream that I'm putting code in the sheet/module rather than an actual module. But I don't think that is the source of the error - correct me if that's wrong.

The "EmailToBeth_Unprotect" Sub call is being made from the sheet/module called "Quote". The actual Sub is in the "EmailToBeth" sheet/module. I sure thought you could call things across different modules.

Edit: It works after moving it to the same sheet/module. But putting everything in one gigantic module will be disorderly with Subs not being logically grouped together with like functions. So how do you call a Sub in a different module?

Any advice? Thanks.

Upvotes: 0

Views: 2041

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

But I don't think that is the source of the error - correct me if that's wrong.

Yes it is wrong becuase EmailToBeth_Unprotect is not visible to your other module. To use it, prefix it with the relevant sheet codename. I would recommend reading Code Module And Code Names.

Here is an example.

'~~> In Sheet1 Module
Sub SampleA()
    Sheet2.SampleB
End Sub

'~~> In Sheet2 Module
Sub SampleB()
    Msgbox "Hello World"
End Sub

Similarly, if EmailToBeth_Unprotect is in Quote then you can call it using Quote.EmailToBeth_Unprotect. If it is in EmailToBeth then call it using EmailToBeth.EmailToBeth_Unprotect.

Upvotes: 1

Related Questions