Workbook_BeforeClose Event - passing arguments

**pmguerra** · 10-08-2008, 02:01 PM

Hi,

I'm trying to use Workbook_BeforeClose event to delete my custom menus.

However, when I try to pass the custom menu caption to the Sub, I get a compile error (Expected Function or Variable).

I believe this has something to do with global variables declaration or something...

Please Login or Register  to view this content.

Thanks in advance!

**Andy Pope** · 10-08-2008, 02:25 PM

Where and how are you declaring the object cbcCustomMenu

**mikerickson** · 10-08-2008, 02:27 PM

I think that the source of your problem is your using Run. Using Call should fix that

Please Login or Register  to view this content.

**pmguerra** · 10-08-2008, 02:27 PM

I'm declaring it in a Module in the VBA Project.

**Andy Pope** · 10-08-2008, 03:07 PM

As mickerson points out your syntax is correct for the Call statement rather than Run.

**pmguerra** · 10-08-2008, 06:15 PM

Now it returns "byRef argument mismatch"

After I put "byVal" in the arguments list, it returns "typemismatch"...

I even tried declaring my variable as GLOBAL but it didn't work...

**Leith Ross** · 10-08-2008, 06:42 PM

Hello pmguerra,

It would help to see the your macro code.

Sincerely,
Leith Ross

**pmguerra** · 10-08-2008, 06:47 PM

I took most of this code from an internet site on building custom menus.

Please Login or Register  to view this content.

Thanks for the help!

**mikerickson** · 10-08-2008, 07:36 PM

That code declares the variable nomeMenu three times.

1)Inside the sub AddMenus, remove the line

Please Login or Register  to view this content.

2)Change the name of the argument in DeleteMenu, e.g.

Please Login or Register  to view this content.

3)The typemismatch is coming from the module wide declaration (before AddMenus) of nomeMenu. In ThisWorkbook, nomeMenu is not declared and therefore assumed to be a Variant rather than a String. Hence the type mismatch.

To fix that, set your VBEditor Preferences to Require Variable Declaration. This will put Option Explicit at the start of all your modules and prevent future problems.

For this specific situation, changing the first line in the posted code to

Please Login or Register  to view this content.

will give nomeMenu a global scope and set things right.

**pmguerra** · 10-09-2008, 07:41 AM

OK! It finally works!

Here's the final code:

Please Login or Register  to view this content.

Another question

To fix that, set your VBEditor Preferences to Require Variable Declaration. This will put Option Explicit at the start of all your modules and prevent future problems.

How do I do this? I had to mannualy insert Option Explicit in every module...

Thanks a lot! You've been a great help!

**mikerickson** · 10-09-2008, 10:24 AM

In my Excel2004 VBEditor, I go to the Excel menu item, choose Preferences from the dropdown list and click the appropriate checkbox on the Editor tab.

In other versions of Excel, the location of that dialog box may be different. Starting a thread with a title "Automatic Option Explicit in Excel xxx" would get a quick responce tailored to your version.