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!"
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
You can find Windows Task Scheduler under the Administrative Tools by clicking the Windows Start button on your desktop status bar. If you enter the macro name for the macro attached to your "Run All" button as the file to schedule and set the parameters for days and timed by following the user friendly menu selections, it should resolve you issue.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
Doesn't the module have to be a public function? currently, everything I have is in Private Sub, this is what happens when I click the button in my Access Application;

VBA Code:
Private Sub cmdsyncrunall_Click()

'Dim lbwait

'Call DriveTest 'Module 'DriveSpace' checks for low drive space, F drive

On Error Resume Next 'Run All'

lbRCS.Caption = "Sync TNS with CIS"

'lbwait.Visible = True

RCS.Visible = False

lbupdate.Visible = True

lbRCS.Visible = True

cmdsyncrunall.Caption = "Wait"

lblwait.Visible = True

RCS.SourceObject = ""

lbrandate.Caption = ""

Dim I As Integer

Dim cl As Integer



    Call metertype 'updates missing metertype 97 &109

    Call meterclass 'updates GEKV2C meter class to 102

   ' Call cmdcycle99  'Cycle 99 update

    Call cmdpremise 'Premise

    Call cmdacct  'Acct

    Call cmdrate 'Rate

    Call cmdcycle  'Cycle

    'Call cmdpropane  'Propane Acct no longer used

    Call cmduser2 'Updates Active Meters reading in TNS user2 notes

    Call cmduser1 'Updates User1 field with DSI Collar SN#

    Call cmduser6 'Updates DRU number in TNS user6 notes

    Call cmdmissingmeter ' Updates missing meter#

'    Call GensEx 'Changes class of Gen to Inactive when GLS expires

    Call delpendingcmds 'Deletes pending SDC Actions and Commands



'lbwait.Visible = False

'Call fhide



lbRCS.Caption = "TNS Desktop Applications"

cmdsyncrunall.Caption = "Run All"

RCS.Visible = True



With DoCmd

    .SetWarnings False

    .OpenQuery "Date_Update_Query"

    .Close acQuery, "Date_Update_Query", acSaveYes

    .OpenTable "Date_table"

    .Close acTable, "Date_table", acSaveYes

    .SetWarnings True

End With

cl = DCount("*", "Meter_Class_Service_Multiplier_Query")

'i = DCount("*", "Accts w/Inactive_Gens and Incorrect Rate Code")

DoCmd.OpenQuery "Estimated_Query"

DoCmd.OpenQuery "AMR_Missing_IntervalReads"



DoCmd.OpenForm "lookup Tasks Queries"



lbrandate.Caption = DLookup("todaydate", "Date_table", "key = 1")

Call countrecords

If RCS.SourceObject = "" Then lbupdate.Caption = "" Else lbupdate.Caption = "Inactive Gen Accts that needs Rate Change"

If cl > 0 Then DoCmd.OpenQuery "Meter_Class_Service_Multiplier_Query", acViewNormal



FinalNote = MsgBox("Sync Complete")



'isdone:

   ' Call cmdexit

End Sub
You can find Windows Task Scheduler under the Administrative Tools by clicking the Windows Start button on your desktop status bar. If you enter the macro name for the macro attached to your "Run All" button as the file to schedule and set the parameters for days and timed by following the user friendly menu selections, it should resolve you issue.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
It might take some reading and experimenting on your part to accfomplish the use of the scheduler. But if the Private in the procedure name bothers you, you could simply rename the procedure to
VBA Code:
Sub myFirstTryAtScheduling()
and copy it to a public module. Then use that name for the scheduler. There are tutorials on the web for using the scheduler if you type "Windows Task Scheduler Tutorial" in the browser window and press enter, it will bring them up for you.l
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
All the reading I came across I was under the impression that I couldn't do any of this if it was Private, that's why I was trying to figure out how to make it public.

the steps I found were

Create a Public Function (not Sub) in the database
Create a Macro in the database to invoke the function:
Create a Windows Scheduled Task to invoke MSACCESS.EXE with the appropriate parameters

Can this be done on private?

It might take some reading and experimenting on your part to accfomplish the use of the scheduler. But if the Private in the procedure name bothers you, you could simply rename the procedure to
VBA Code:
Sub myFirstTryAtScheduling()
and copy it to a public module. Then use that name for the scheduler. There are tutorials on the web for using the scheduler if you type "Windows Task Scheduler Tutorial" in the browser window and press enter, it will bring them up for you.l
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,669
Office Version
2013
Platform
Windows
The sub needs to be a public sub if you are going to call it from a scheduled task. Just change Private to Public. As far as I know it can be a sub or a function, shouldn't matter.

*But* if its not in a regular module (for instance, if its in a form) that could be a little tricky. Hopefully that's not the case.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
how can I tell if it's a regular module or not?

and to confirm, I can just go in and switch "Private" to "Public?"

Let's just say I'm jumping in head first
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
You have code modules for ThisWorkbook, Sheets, UserForms and public. The public ones are the one you insert and Excel gives them a number like Modul1. The others are all built in with the Excel application and the Private in front of the Sub name indicates that it will only run from events in that host object. If you open the vb editor, you will see the different modules for that workbook in the Project pane at upper left on the editor window.If it starts with "Module" it is a public code module and there can be more than one of them. These can also be used to create a new Class and would then be identified with an icon different than the public modules.

More here: Code Modules And Code Names
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
didn't realize you were in Access, thought you were in Excel. But any of those listed under 'Modules' should be OK to put the macro into and call it from the Task Scheduler.

1592512287426.png


This is more what I was expecting to see.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,101,935
Messages
5,483,779
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top