Converting Private Sub to Public Function

[ctackett6407]

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

 

[ctackett6407]

@xenou now that makes sense to me.

I just need to put the sub in a public module. I'll try this out.. I'll probably try everything but this one seems simple enough if I can make it work.

Example of running an access VBA function using WTS:

Auto Trigger MS ACCESS Code

hey there, I've recently switched to window 7. previous on XP i used task scheduler to open a MS Access database at a particular time daily where there was code attached to a form (which was opened when database opens) that runs code if opened at the correct time (when the scheduler opened...

www.mrexcel.com

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]

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.

You're welcome, good luck.
regards, JLG

 

[ctackett6407]

@xenou

I used your post and created the following based on your post but here's what happens.

I double click the vbs and the file opens! (success one checked off the list)

When the .mdb file is opened the creator of the application (no longer works with us) .. has a prompt that opens that has a "Yes" or "No" option. We ALWAYS click yes. There's no reason to never click no that I know of.

That is as far as I get after I click the .vbs file.

Is there a way to by pass the prompt that asks me to click Yes or No? because no one can tell me if there is a time when "no" is an option. I'm sure the original person that wrote this has a reason but he is retired.

On Error Resume Next
Call Run_Job()

'__________
Sub Run_Job()

    Set objAccess = CreateObject("Access.Application")
    With objAccess
        .OpenCurrentDatabase "S:\Path To File\Filename.mdb", False
        .Run "cmdsyncrunall_Click"  '//if the routine is a function not a sub: .Run ("Scheduled_001")  
        .CloseCurrentDatabase
        .Quit
    End With

End Sub

now I wasn't sure if I was supposed to make a whole other module or just make the section in this module "public" .. I went in and made the "Private Sub" into "Public Sub" and left it in that module.

Public 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
Private Sub delpendingcmds()
'deletes action and pending sdc commands

lbRCS.Caption = "Deleting SDC Pending Actions and Commands"

lbupdate.Caption = "Deleting pending and action sdc commands"

With DoCmd
    .SetWarnings False
    .OpenQuery "DeleteSDC_PendingActions"
    .OpenQuery "DeleteSDC_PendingCmds"
    .SetWarnings True
End With
lbupdate.Caption = ""
End Sub
Private Sub cmdsyncuser1_Click()

lbRCS.Caption = "Adds DSI SN# to User1 Field and Updates DSI Association"
'Call cmduser1
'updates DSI Collars in User 1 Field

lbupdate.Caption = "Adding DSI to User1 Field and updates DSI Association"

With DoCmd
    .SetWarnings False
    .OpenQuery "TNS_Update_Meter2DSI_Query"
    .OpenQuery "TNS_Update_DSI2Meter_Query"
    .SetWarnings True
End With
lbupdate.Caption = ""

End Sub

I also noticed that there are various areas in this module that use the "cmdsyncrunall" wording

I wasn't sure if that impacted anything that I'm trying to do.

Private Function TNS_Update()
On Error Resume Next
Application.Visible = False
DoCmd.Minimize

Call cmdsyncrunall_Click

Application.Quit
Kill File = "w:\databases\TNS_desktop_Apps\check.txt"
End Function

Private Sub Sync_TNS_to_CIS_Click()
lbRCS.Caption = "TNS Desktop Applications"
RCS.SourceObject = ""
RCS.Visible = True
cmdsyncrunall.Caption = "Run All"

Private Sub Home_Click()
On Error Resume Next
lbRCS.Caption = "TNS Desktop Applications"
RCS.SourceObject = ""
RCS.Visible = True
cmdsyncrunall.Caption = "Run All"
chksubmeters = False
End Sub

 

[JLGWhiz]

The Public and Private terms associated with subs can be confusing when when trying to align them with code modules. But it is really pretty simple.
1. Worksheet, Workbook(ThisWorkbook) And UserForm code modules all use Private Sub as the start of their procedure titles because they generally are codes triggered by events within those objects. While the codes withing these modules can be made to run with vba, they are not listed in the Macro dialog box as a publicly accessible code.

2. Code listed in the Macro dialog box is publicly accessible and can be called and run from any other code module in Excel.

3. Code modules which are inserted by the user and automatically numbered by Excel starting with Module1 are considered public code modules and any code within these modules are deemed publicly accessible and can be called from anywhere in the Excel application.

4. Code in the public module does not require the word public as part of its title. Conversely, putting the word public on a code title and leaving it in a Sheet, Workbook, or UserForm module will NOT make it globally accessible. To be globally accessible, the code must be in a code module that allows global access.

So, your code title for the code being called from the WTS needs to have the word private removed (If it was a private sub) and for the sake of avoiding confusion, remove the _Click if the code was used for a click event on a control. Put the code into a User inserted code module (does not matter which one, but Module1 is as good as any). Then the code widll be accessible from the WTS via the File Name that the code resides in.

That was for clarification on Private and Public.

As for your Yes/No prompt. I don't find it in the code posted, but my eyesight is not what it used to be. When you get the prompt, maybe you could press Ctrl + Break keys and see which macro and which line of code is highlighted. Then I could work with it.

 

[xenou]

Generally I would add:

1) it shouldn't affect your code if you change "private" to "public". This is going from a more restrictive scope to a less restrictive scope. So the code that had access to it as private will still be able to use it as public.

2) I would try to find and disable that prompt. The long and short is that if you want something to be automated, you really want to get rid of buttons that need to be clicked (basically nobody is there to click the buttons so its just a problem and not an easy one to solve. There may be ways to configure said buttons to figure out if the application is being run manually with a human user or not. However, not always. So the best solution is not to have these kinds of buttons at startup.

@JLGWhiz, correct me if I am wrong but I think if you put a private function in a public module it is only accessible in the module where it is declared. Also you can call a function or sub in a worksheet module (btw the OP is working in MSAccess). For instance (where ws_foo() is a public function in a Sheet1 code module that simple returns the number 1):

'// Code in a module
Sub bar()
Dim i as Long
    i = Sheet1.ws_foo()
    Debug.Print i
End Sub

 

[JLGWhiz]

@@JLGWhiz, correct me if I am wrong but I think if you put a private function in a public module it is only accessible in the module where it is declared. Also you can call a function or sub in a worksheet module (btw the OP is working in MSAccess). For instance (where ws_foo() is a public function in a Sheet1 code module that simple returns the number 1):

No argument from me on that. I was just trying to shed some light of the use of public and private in naming subs. Also aware that OP is in Access. Tks. JLG