Open a VBA Macro when Excel Opens

mlocmcgash

Hi folks,

I have a macro that I wrote in VBA that I want to open as soon as I open Excel (it's part of a bigger process and ideally we don't want to have to be there to kick off this Macro......we have a way to open Excel automatically as part of the process).
I can get the worksheet to open as Excel opens alright by placing it in the 'Xlstart' folder under my profile.
I have read 2 different ways for me to do it also:
1. Name the macro Auto_Run
This doesnt do anything for me
2. Put the code into the 'workbook_open' routine of the 'ThisWorkbook' method

Found info here

When I try this I get errors, something to do with how I have placed the code in I think.


My Macro code has the following format:


Option explicit

Dim AllMyVariables

---

Sub format

'Some code'

Call Sub_a

'Some more code'

Call Sub_b

'Even more code'

End Sub

---

sub Sub_a

end sub

---

sub Sub_b

end sub

---

My Q is, in order for it to work in the 'workbook_open' routine, does it have to be placed somewhere in particular or does it have to be called from the routine (though I've tried this and it didn't work).
If you need actual code I can give it, just saving space.
Also, any other way to do this would be greatly appreciated.

Thanks a million.

bonkey

mlocmcgash wrote:

does it have to be called from the routine

Y'huh.

You define your own functions however/wherever you like, and then call them from this workbook_open event.

One thing to bear in mind is that for smoe time now, Excel (and office in general) have had a variety of different configurations which limit/prevent this type of macro from working, because these were the mechanisms by which macro-virii were spread.

So it may not be that your code is wrong....you need to check that Excel isn't blocking auto-open macros from running or anything like that. Can't remember how thats done, off the top of my head, but its in the Options somewhere...and it may depend on the version you have anyway.

jc

mlocmcgash

Thanks bonkey, I'll look into this.
If I'm calling the Macro what name do I call it by?
The name of the main sub is 'Format' but I have a button set up on the excel toolbar to run this, named 'Regression'. Which of these would you put after the 'Call' function? I've tried both but to no avail (but as you said it might be another problem....but I'd like to know which one so I can have it right anyway).

stevenmu

'Format' is the one you need to call, also put a msgbox("workbook open") in the workbook_open sub so you can see if it's at least getting that far.

bonkey

stevenmu wrote:

'also put a msgbox("workbook open") in the workbook_open sub so you can see if it's at least getting that far.

Exactly what I was about to suggest.

jc

mlocmcgash

stevenmu wrote:

'Format' is the one you need to call, also put a msgbox("workbook open") in the workbook_open sub so you can see if it's at least getting that far.

Tried that guys, it skips the msgbox and goes straight to 'Call Format' highlights it and gives me an error:

"Compile Error:
Argument not optional"

when I click ok it highlights Private Sub Workbook_Open in yellow.

I'm working on Excel 2000 if that's important info and I have Macro security set to Low.

stevenmu

Ok, that errors happening before it actually runs anything. It's expecting a parameter(s) to be passed in to 'Format' even though your doesn't seem to have one. It's possible, pretty likely actually, that there's some inbuilt Format sub/function that it thinks you're trying to call. First thing to try is changing the name of your sub and the calls to it to something more unique and see if that helps.

mlocmcgash

Didn't show the message again, did the same thing only this time the error was:
"Sub or function not defined"
I renamed the sub Format to sub Main_Code and did 'Call Main_Code'.
Any ideas....hope it's not something simple I'm missing (actaully hope it is).

bonkey

OK...Do this...

Open Excel with a new, empty Worksheet.
Open the Visual Basic Editor (Tools / Macros / Visual Basic Editor)
In this, select the ThisWorkbook Object from the project explorer.
when the code window opens, go to the left-hand combo in the editor window, and select Workbook.
This *should* create a "stub" for Workbook_open:

Private Sub Workbook_Open()

End Sub

In here, add only the following:

MsgBox("test")

Hit the "Play" button, and yuou should see a message box with the word test in it.

If that works....

Add a module to the project, and paste your code into it.
Add a call to format below the MsgBox code above, so your code looks like::

Private Sub Workbook_Open()
MsgBox ("test")
Format
End Sub

(You can use Call Format instead of just Format if you like).


If this is telling you Argument Not Optional, then Format is not defined as :

Sub Format()
  ...
End Sub

But instead is defined to take parameters. If thats what the problem is, then you need to find out what parameters with what values are required, and supply them.

jc

RicardoSmith

mlocmcgash wrote:

...I have Macro security set to Low.

Really bad idea. Especially if its on a live system. Use SelfCert to create your own digital certificates.
http://support.microsoft.com/?kbid=217221

dave.h

Hi,

Your problem is probably that the function Workbook_Open is in the ThisWorkbook codebook/module whereas your function code is in a proper module. This means that if your function code is in a private subroutine it cannot be called. Try changing from "Private Sub" to just "Sub" or even "Public Sub" and see if that makes a difference. It should.

Dave.