Converting Private Sub to Public Function

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
So I attempted to make a macro and I selected these options

1592512723466.png


when I attempt to run it I get the following

1592512751435.png
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,270
Office Version
2013
Platform
Windows
1. copy the code to a public code module.
2. Open the vb editor and click on Tools>Macros
If your macro does not appear in the dialog box then it is not in a public module.
3. Omit the Parentheses ( ) from the macro name when entering in the scheduler. They are not part of the name.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,878
Office Version
365
Platform
Windows
If that is a sub, you can't call it from a macro or anything else that requires use of a function. Since it looks like a click event for some control, and such events are subs, not functions, that would be an issue. If you need to run a sub, you'll have to create a function and use it to call the sub.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
I'm learning and I have microsoft documents pulled up while I type this.

To do what you suggested @Micron , do I create a module and then put the function in the module that calls the sub?

I have to look up how to do this.

You are correct by the way. I click on a button on a form that runs this sub. It's a "Run All" command that runs a bunch of queries.

If that is a sub, you can't call it from a macro or anything else that requires use of a function. Since it looks like a click event for some control, and such events are subs, not functions, that would be an issue. If you need to run a sub, you'll have to create a function and use it to call the sub.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,878
Office Version
365
Platform
Windows
A function called by a ribbon button, toolbar button or a macro has to be in a standard module - and do not give them both the same name. So if you must initiate this from a macro, then yes, I would create a module (e.g. mdlDbFunctions) and put a function in it. Use the macro action to run the function and call the button click event from the function. You will have to change the Private keyword on the button click code (assuming you have any) to Public or it won't work.
I'm not understanding why macros are involved in this if you are using code.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
@Micron I'm trying to automate a process I have to do randomly all day.

I have to open an .mdb file and click on the "Run All" button and it goes through a ton of queries and updates various things based on criteria. I'm hoping if I can figure something out that I can set this up to run every few hours on it's own and I can stop doing it.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,878
Office Version
365
Platform
Windows
EDIT - your function will likely have to set the focus to the form first if you attempt to do anything within the button click code that requires it to be so.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,878
Office Version
365
Platform
Windows
Did you consider task manager to initiate the process according to your needs?
I don't have your db or fully understand the process, but I have done a similar sort of thing before, and the only macro I needed was AutoExec so that it would run when the db opens. Apart from that, there were no other macros - all functions and subs, all automated.
 

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
@Micron This is what I found online.

Goal: is to be able to do the "Run All" command on a schedule without having to leave the database open or have to monitor it at all.

I found some stuff on another site that read that I should make a macro, tie it to a batch file, then hook the batch file into Windows Task Scheduler and set it up that way using a command code with the function tied to the end of it? Something along those lines.

This is a huge learning process for me, but I'm learning a lot in the reading and discovery.

This application was written years ago by someone who was versed in Access and VB. Right now I open this report and click the Run All and 15 or so minutes later it tells me it's complete. The report goes through all kinds of queries and syncs up data between two databases and populates fields i one database that are found in the other (syncs).

When people work their job all day they end up calling me and asking "hey sync the database please" and I have to keep stopping what I'm doing or if I'm in a meeting I can't do it. It also stinks when I take a day off because I have to log into my computer and update the system.

So this was a solution I was trying to come up with.

Did you consider task manager to initiate the process according to your needs?
I don't have your db or fully understand the process, but I have done a similar sort of thing before, and the only macro I needed was AutoExec so that it would run when the db opens. Apart from that, there were no other macros - all functions and subs, all automated.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,878
Office Version
365
Platform
Windows
Well, that doesn't explain why you need a macro, but no matter.

Not that I say this was any better, but I also had a multi-user situation with an autoexec macro that called the startup code. If the db had no Command property value then a person was opening the db and automatic updating part didn't run but the user validation part did. The db had a button so that the user could perform their own update if for some reason the automatic update didn't happen the night before. Can you not just let them do their own updating?

Anyway, the TS line to open the db at night had a command line switch, which when used to open a db, sets the db Command property value to whatever the switch is. In that case, the code would validate that it was a pc (task scheduler) opening the db, so it would automatically update the back end tables and skip anything that had to do with logging anyone in and so on. Before doing an update, I created a backup file in case it all went south. Then performed the updates and if OK, then over-wrote the backup at that point. This way, if anything bad happened, I had all the user modified data up to the point just before the backup. That happened whether or not TS or a person opened the db. Maybe some of that will provide a bit of insight to just one of what must be a hundred ways to do this.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,014
Messages
5,508,796
Members
408,694
Latest member
LightBright

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top