How exactly does VBA code in a spreadsheet run automatically?

[jtpryan]

I have inherited some workbooks from a coworker that left. Needless to say they are not documented. So I have done some VBA coding and can debug most of it. But, I cannot get a handle on what is probably a very basic understanding. When I open a workbook (.XLSM) nothing happens. That is, the code behind it never runs. There are no buttons in many of these workbooks to activate the code. I can, of course, open the code window and click the run button to make it happen, but these would, in my understanding, do their thing just by opening it and in fact run at regular intervals while open.

This is something I've never understood about coding in Excel. How to make the code "start" without a button to kick it off. I don't really understand the structure either. I have, in the left window a tree consisting of the sheets in the workbook, followed by a "modules" section with some numbers of modules below it. I find no code in the sheet section, only in the module section. How exactly does that work?

This is probably a result of learning to code ***-backwards, that is having a problem and copy/pasting/modifying code to make it work. So I have never really gained an understanding of the structure behind it. Any pointers would be appreciated.

I'm thick skinned, so feel free to have some fun at my expense if I'm missing something obvious...

Jim

 

[Logit]

There are several ways to run the code in those modules.

In the "ThisWorkBook" module (at the bottom of the tree displaying all the Sheets), there is a workbook command 'Workbook_Open () '. Depending on the circumstances of what the code is
intended to do, placing a 'call' to the specific macro (section of code in the modules area) will initiate the code when the workbook is opened.

You can do the same thing with a 'Workbook_Close () ' event. Stop something for occurring when the workbook is closed.

Most macros are usually run by a Command Button. Those are usually placed on the worksheet or a UserForm. Clicking the button is like pressing the button on your cell phone. The button is attached to
only one section of code. It does one thing - activate the macro code and nothing else.

The only way to determine what the workbooks do and/or how it is done would be to review the macro codes in the modules and compare that to the worksheet as well.

This forum doesn't allow posting workbooks. You can post the code however, for review.

Or you could post the workbooks to a Cloud site (Google, DropBox, Amazon, etc.) for download.

Being able to see and work with the actual workbook is the quickest and most accurate method of reviewing the projects.

What is your pleasure ?

 

[jtpryan]

Nice, you answered a big question. So, this one workbook has a module that I open the code window and click the "play" button and from there on it runs all night as I want. The first line in that module is "Sub rfit()". If I go to "ThisWorkbook" in the "workbook" area I see:

Private Sub Workbook_Open()


End Sub

How would i call the rift() subroutine from there?

I may take you up on posting the code to a cloud area. But for now this might answer quite a bit.

Jim

 

[Logit]

[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]
[COLOR=#333333]
rift[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

Earlier versions of VB/VBA required the syntax "Call rift" in order for the program to understand you were referencing (calling) a macro (rift).
Present versions of VBA understands without the word "Call".