Beginner at VBA

[Bob Simon]

I'm beginning to teach myself VBA using Super-Easy Guide to the
Microsoft Office Excel 2003 Object Model
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/html/odc_super.asp

I'm stuck at the bottom of Lesson 4 where they are trying to show me
how to make Excel automatically run a macro when it starts up. The
timesheet macro runs fine by itself but when I put it in here:
Private Sub Workbook_Open()
TimeSheet
End Sub
a window pops up that says:
Expected variable or procedure, not module

What am I doing wrong?

 

[KRCowen]

Bob

Have you named the module that holds the "Timesheet" macro "Timesheet"? That
would cause your problems. You cause lots of problems when module names and
macro names are the same.

Good luck.

Ken
Norfolk, Va

 

[Gord Dibben]

You have probably named the module as TimeSheet, which is the same name as the
TimeSheet macro.

Change one or the other.

The lesson says to name the module TimeSheets(note the "s")

Gord Dibben Excel MVP

 

[Bob Simon]

That was it, exactly. Thanks!

You have probably named the module as TimeSheet, which is the same name as the
TimeSheet macro.

Change one or the other.

The lesson says to name the module TimeSheets(note the "s")

Gord Dibben Excel MVP

 

[Bob Simon]

I renamed the module to "timesheets" and it ran fine when I re-started
Excel. However, the macro is still named "timesheet". Shouldn't
Excel automatically rename the macro when I rename the module? In any
case, how do you do this manually?
Bob

 

[Tom Ogilvy]

The point was that the module and the macro should not have the same name.
So no, changing the name of the module should not affect the name of the
macro. The macro is named in the declaration

sub TimeSheet()

end Sub

you would change it (as an example)

Sub MyTimeSheet()

End sub

Now you would have a macro named MyTimeSheet and no longer have a macro
named Timesheet. Not that any code that refers to the timesheet macro would
have to be changed manually as well - excel will not change it. this is not
the case for changing the module name unless you had specifically referred
to the module in your code.