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

Question

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:

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.

It's not so much "a sub in a different module" as "a sub in a document module", or more precisely, "a sub in any class module with a VB_PredeclaredId attribute set to True". If you have a not-private sub procedure in a standard module (like any of these Module1-Module4 modules), then you can invoke it from anywhere in the project by name, like you did. — Mathieu Guindon
– Mathieu Guindon, Commented May 27, 2021 at 23:53

Siddharth Rout · Accepted Answer · 2021-05-27 21:18:57Z

1

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.

edited May 27, 2021 at 21:18

answered May 27, 2021 at 21:13

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related