Converting Private Sub to Public Function

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
I have been on a mission to help automate a task I do every morning.

A person created an Access application years ago that we still use to compare two databases and kind of run all kinds of queries and tables. When I open the application I click a "Run All" button and then it just goes off and running and completing the list of commands inside the modules.

I've asked questions and poked around and found that I might be able to get this to work with Task Scheduler if I create something as a Public Function , add a macro somewhere, and then use Task Scheduler to invoke the access application with some type of parameter.

I'm been trying to figure out where to start on this as I reviewed the application the person created and everything in it is Private Sub.

Do I have to convert this or can I convert this? I'm hoping I'm going to be told to "run a replace all for Private Sub, because it's just that easy!"
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,432
Office Version
  1. 2013
Platform
  1. Windows
@ctackett6407 - You already had all the elements of code that you needed to do what you want. All you needed to do was move your 'Run All' button code to a public code module, remove the 'Private' from the macro name (and for cosmetic purposes, maybe remove the '_Click'), then put the macro name into the Windows scheduler without the parenteses.. Your button code will serve as a batch file to initiate the other macros that do the various tasks.
 
Last edited:

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
@JLGWhiz let me give that a try. I'm literally learning as I go and reading anything online that I think fits into what I'm trying to do. Thanks for all the help folks as I try to get this working.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
880
Office Version
  1. 2013
Platform
  1. Windows
@ctackett6407 put the macro name into the Windows scheduler without the parenteses.
Just an observation... how would Windows Task Scheduler know what file to open and how would WTS be able to launch the desired "public" macro?
WTS accepts almost everything you type in without an error message, but if it cannot be executed then nothing will happen.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
I'm going to have to reread this thread and regroup, I got myself lost in what I'm doing. lol

but I do thank people for the help.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,432
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Just an observation... how would Windows Task Scheduler know what file to open and how would WTS be able to launch the desired "public" macro?
WTS accepts almost everything you type in without an error message, but if it cannot be executed then nothing will happen.
If you go back to the beginning of this thread, the OP states that the button macro then calls other macros which do all the work. So the only code the scheduler needs to call is the button macro, converted to a public procedure in a public module. That macro will then call the other necessary procedures to do the tasks.. The problem the OP is having is getting the right procedure name in the scheduler and having that procedure in the correct code module so the scheduler can find it. There is not a problem with the code, the problem is getting the scheduler to call the code. The OP did not understand what comprises a procedure name and the nuances of _Click events, etc. But once they get that straightened out, it should work.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
880
Office Version
  1. 2013
Platform
  1. Windows
I understand what the OP wants, but you simply cannot enter a macro name in WTS. You can (because you can type anything you want) but the macro will never be executed. However, you can schedule a VB script in WTS and run the macro using this VB script.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,690
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Example of running an access VBA function using WTS:

See Post 6.

Other ways of using WTS are of course possible. This could probably be adapted to run a macro pretty easily (my example runs a VBA Sub or Function in a public module).
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,432
Office Version
  1. 2013
Platform
  1. Windows
I understand what the OP wants, but you simply cannot enter a macro name in WTS. You can (because you can type anything you want) but the macro will never be executed. However, you can schedule a VB script in WTS and run the macro using this VB script.
Well, I won't get hung up on semantics. If the user follows the menu to create a task, then the task should run when scheduled. There are tutorials on the web to show how to create a task. And that is the part the OP is having difficulty with. I am sure he would appreciate any help along those lines.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,432
Office Version
  1. 2013
Platform
  1. Windows
This is a cut down version of the turtorial for adding a VBA macro to the scheduler. You can try it and see if it takes.

1. Get your macro into the correct code module and correctely named.
2. Open task scheduler (Control Panel>Administrative Tools>Task Scheduler)
3. Select Create Basic Task and type a name for the task (not macro name)
4. Click Next button
5. You can now select an exact time for the macro to run or "When I Log On"
a.Select 'Daily'
6. Click Next button
7. Enter the date and time to start running the macro, It will then run every day there after.
8. Click Next button
9. Select 'Start a program'
10. Click Next button
11 Enter the macro name enclosed in quotation marks ( "macroname") in the Program box.
a. In the Add arguments box enter the full path to the file holding the macro in quotation marks.( "C:\lev1\lev2\myFile.xlsm")
12. Click OK
You won't be able to locate the macro using the browse button at step ll because it only goes to the file folder level for Excel files. You haveto type in the macro name.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
Thank you for this, I'm revisiting it today as it got myself confused and I had to step away and work on something else. I really thank you all for replying.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,937
Messages
5,525,728
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top