3

I have a macro that automatically opens double-clicking Excel workbook using: Sub Auto_Open() with script in a module, but same script in VB editor ThisWorkbook with no modules using: Private Sub Workbook_Open() doesn't work.

Any ideas why, and is there any advantage to directly auto-opening from ThisWorkbook? I don't have a need for any modules if the latter could be accomplished.

hidefguy
  • 61
  • 1
  • 7
  • 1
    "doesn't work" is a rather poor description of what's going on. Can you [edit] your post to expand a little? If you put a `MsgBox` call in it, save, close, and re-open (with macros enabled), do you get the `MsgBox`? – Mathieu Guindon Jul 12 '18 at 02:03
  • Best way I can describe. No message boxes. Just doesn’t work as intended according to MS docs when double-clicking Excel icon/file. Macros are enabled and it’s a .xlsm file. – hidefguy Jul 12 '18 at 02:10
  • 1
    Verify your macro security settings, and then in the VBE place the cursor inside the procedure; verify that the left-hand-side dropdown says "Workbook" and that the right-hand-side dropdown says "Open", at the top of the code pane. If left-hand-side dropdown says "(General)", there's a typo in the method name, or you're not in the `ThisWorkbook` module. – Mathieu Guindon Jul 12 '18 at 02:13
  • 1
    You nailed it. I does indeed say General. Thanks – hidefguy Jul 12 '18 at 02:24
  • 1
    Awesome! You can click the hollow checkmark underneath the up/down voting buttons to mark an answer as "accepted", which effectively takes your question off the "unanswered" list. Cheers! – Mathieu Guindon Jul 12 '18 at 02:27

1 Answers1

5

[Public] Sub Auto_Open() in a standard/procedural module is the old/legacy way to go. Nothing besides its name says "this procedure runs automatically when the workbook is first opened", how it's wired-up to run on open is kind of magic: a public procedure by that name runs on open, and who knows what happens if you have two such procedures in two separate modules. The name isn't standard either: the well-established and rather familiar Interface_Member convention (with the underscore between the two identifiers) isn't respected here: there's no Auto object in the Excel object model.

Private Sub Workbook_Open() is an event handler procedure that handles the Open event of the Workbook interface, which ThisWorkbook implements; like Auto_Open, it runs when the workbook is opened, however its mechanics are much more transparent, and work exactly like every other event-handling procedure: Workbook is the interface, Open is the event/member.

It being a Workbook event has interesting implications: a workbook that's programmatically opened (from VBA code) will always open with macros enabled - and if that workbook has a Workbook_Open handler, then that macro will run - however it being an actual event means your calling code can decide to switch it off with Application.EnableEvents = False.

As for it not running, barring macro security settings the only reason I can think of is if there's a typo in the name. Never type event handler signatures by hand - by using the dropdowns at the top of the code pane, you make the VBE generate the correct event handler signatures for you.

You know you're looking at an event handler procedure when the left-hand-side dropdown contains the name of the object/interface, and the right-hand-side dropdown contains the name of the event/member:

Workbook_Open: "Workbook" on the left, "Open" on the right

WorkbookOpen: "(General)" on the left, "WorkbookOpen" on the right

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Mathieu. Should I just use Open in right drop down and apply code between Private Sub Workbook_Open and End Sub? I have no type of event handling in script. – hidefguy Jul 12 '18 at 02:47
  • @hidefguy Yup. A `Sub` statement creates a *scope*, in which you can declare *locally-scoped variables* that can only be accessed from inside that procedure. In VBA any executable statement *must* be inside a procedure scope. Declaring a variable at the top of the module, in the *declarations section* where you put `Option Explicit`, makes that variable accessible everywhere inside that module if declared with `Private` (or `Dim`, but I prefer to keep `Dim` for locals), and everywhere inside the project with `Public` (or `Global`, but that's obsolete and exactly like `Public` anyway). – Mathieu Guindon Jul 12 '18 at 02:59
  • Procedure declarations themselves (`Sub`, `Function`, `Property` statements) can (should) have an explicit access modifier (`Public`, `Private`, *`Friend`*). If a procedure doesn't have one, it's `Public` by default. Procedures, like variables, should have the tightest scope as possible. Procedures can have parameters, passed `ByRef` by default, or `ByVal` if specified as such; `Function` and `Property Get` accessors can (should) have a *return type*, too. By passing parameters to other procedures, you easily reduce/eliminate the need for global variables. – Mathieu Guindon Jul 12 '18 at 03:04
  • Please see my EDIT in my original question. Ran into a weird snag beyond my comprehension – hidefguy Jul 13 '18 at 03:24
  • 1
    Please don't deface your question like this... SO isn't a discussion forum, it's Q&A - if you have a new question, *ask* a new question! ;-) ...and make sure you include the *actual code*, not a screenshot of it. That said, it looks like there's no window named "LSE" in the `Windows` collection. Why do you think you need to work with individual windows? – Mathieu Guindon Jul 13 '18 at 03:29
  • 1
    Declare a `Workbook` object; `Dim wbLSE As Workbook` then `Set wbLSE = Workbooks.Open(...)` - the `Workbooks.Open` method is a *function* that returns an object reference for the workbook it opened: use it! – Mathieu Guindon Jul 13 '18 at 03:30
  • Apologize for the question "botch up". Simply a neophyte here, but I love it already. So basically you're saying I can just eliminate the Windows visible = false line and it wouldn't slow the script? – hidefguy Jul 13 '18 at 03:37