Converting Private Sub to Public Function

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
@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:

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).
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Mar 18, 2018
Messages
64
@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.

VBA Code:
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.

VBA Code:
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.

VBA Code:
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
VBA Code:
Private Sub Sync_TNS_to_CIS_Click()
lbRCS.Caption = "TNS Desktop Applications"
RCS.SourceObject = ""
RCS.Visible = True
cmdsyncrunall.Caption = "Run All"
VBA Code:
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,669
Office Version
2013
Platform
Windows
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):
VBA Code:
'// Code in a module
Sub bar()
Dim i as Long
    i = Sheet1.ws_foo()
    Debug.Print i
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,033
Office Version
2013
Platform
Windows
@@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
 

Watch MrExcel Video

Forum statistics

Threads
1,101,936
Messages
5,483,784
Members
407,411
Latest member
belladaris12

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