Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to find/edit a Sub with Arguments in the Visual Basic window?

0

Over the past few days I created a macro routine to copy/paste rows from different sheets to one master ToDo sheet based on date.  There are three options: today's date, tomorrow's date, and special date (specified in a cell).  I had it all working well within the code.  I expected to have three different subs to run each type, feeding into the main block of code to do the work.  

I kicked it up a notch by creating three buttons on the ToDo sheet for Today, Tomorrow, Special.   I followed online articles (such as https://www.teachexcel.com/excel-tutorial/1893/pass-arguments-to-a-macro-called-from-a-button-or-sheet-in-excel) to add an argument named WhatDate to the MainControl sub and then to add the parameter to the AssignMacro of the buttons:

'MainControl "today"'
'MainControl "tomorrow"'
'MainControl "special"'

Everything worked great!  I saved and closed out.  Today, I need to make a tiny adjustment to the code.  I can't find the vb code anywhere in the Microsoft Visual Basic for Applications window.  How can I get back to my code?  

I've read articles explaining why it doesn't show up in the Macros list and that makes sense - you can't run it without the argument.  It must be "run" from the button.  But certainly there's got to be a way edit, to get back to your code to continue developing - right?  What am I missing?

---------------

So in talking this through with my wife (which, of course, I should have done first), I just had this idea that works:

Write a new macro that immitates the button.  The only thing in it is:

'MainControl "today"'

Using F8 to step through the macro, it shows me the rest of my code.

Other than this workaround, any other/better way of doing it?

Answer
Discuss

Answers

0

In the VB Editor window, press CTL+R or select Project Explorer from the View menu. This will display the Project Explorer pane in the top left of your screen. Actually, it's there by default but I assume that you must have closed it without intending to.

In the Project Explorer pane you see a list of all code modules in the project. There is one module for each of the worksheets and one called ThisWorkbook. All of these are linked to the tabs or the workbook in a special way. Use them to capture workbook or worksheet events. You don't have such code and therefore they are all blank. They can't be deleted in the VBE. But if you delete a sheet its code module will be deleted with it, meaning you will lose any code that was stored there without particular extra warning in that regard. So, you better know that.

Finally, you have two standard code modules, both of which you created and failed to give proper names. Module2 contains a small macro which you forgot to delete. And, finally, Module1 contains your "lost" code. You can switch between code modules by clicking on the module's name in the Project Explorer.

And now, if I may, one very short word of advice for your coding. Don't Select or Activate anything. By extension, don't use the Selection, ActiveCell or ActiveSheet objects. Applied:-

' In place of
    WorkSheets("ToDo").Select
    Range("A2:L51").Select
    Selection.ClearContents
' simply use
    Worksheets("ToDo").Range("A2:L51").ClearContents

Note that "Sheets" and "Worksheets" are different collections. Since you only have worksheets (no chart sheets or pivot sheets) it makes no difference but you will be well advised to avoid using objects you don't fully understand.

Finall, here is the one exception to the "never" rule:-

' In place of
    Sheets("ToDo").Select
    Range("A1").Select
' use
Worksheets("ToDo").Activate
Range("A1").Select

The reason for the exception is that you are activating and selecting something for the user. VBA doesn't need that but the user does. So, Activate and Select are for the user, as are the associated objects ActiveSheet, ActiveCell and Selection. For VBA's use, assign everything to named variables, starting with the code modules, and then use the name to address it.

Discuss

Discussion

Thanks for the advice.  I'm 100% self taught on this, mostly by recording macros and seeing what Excel put in the code or by searching for examples online.

Regarding the beginning of your answer, how do you get to the VBE window if all (in my case, both) of the macros take arguments and therefore are hidden?  The only way I've ever known for getting to that window is editing an existing macro from the Alt+F8 macros list.
thebigbarn Jun 21, '21 at 11:54 pm
You are very familiar with the VB Editor but aparently don't know its name. Change from the Excel interface (a workbook tab) to the VBE window by clicking the Visual Basic button on the Ribbon's Developer tab or by pressing ALT+F11.
Variatus (rep: 4889) Jun 22, '21 at 7:57 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login