Converting Macros to VBA code

[Lou_Reed]

I am trying to convert MS Access 2010 macros in my db to VBA code. Everything goes smoothly right up to where it says
conversion complete and successful.

But, where is the VBA code? It puts me on a form's VBA code and leaves it at that.



I am not sure where the code is. You certainly cannot learn anything from VBA source, since this code that it shown is
previously written VBA SUBS - nothing new there.

it seems to have disappeared and is lost. Where is the new VBA code?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[JoeM]

Under the Modules object, you should see a new Module whose name starts with "Converted Macro -".
You can also see it from the VB Project Explorer under Modules.
The converted macro VBA code will be in there.

[Lou_Reed]

Okay, I open the Macro in design view. Running across the top of the screen is the navigation pane at the far left, the macro (in design view) is in the middle and the Macro Action Catalog is at the far right.

I click on the convert macro to VBA code on the ribbon that runs across the top of the screen.

The software asks a few questions. I answer them. Then it seems behind the macro in design view I see the VBA code. Still we now have the macro and the VBA code. We have both.

I want to delete the macro and retain the VBA code (as long as it works) and save the VBA code of the translated macro.

At some point I expect to see it (the VBA code) in the navigation pane on the far left and in place of the macro, but not in the macro section.

That is where I am and that is the part that I cannot get correct. The VBA code should now be connected to the button that the macro was once connected to, so it does the same action when the button is clicked that the macro would have done.

How do I do that?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed
.

[JoeM]

I want to delete the macro and retain the VBA code (as long as it works) and save the VBA code of the translated macro.

You can do that. There is no dependency/linkage between them.

At some point I expect to see it (the VBA code) in the navigation pane on the far left and in place of the macro, but not in the macro section.

It will be in the Object Explorer on the right. That shows all the Objects, namely Tables, Queries, Reports, Forms, Macros, and Modules.
This will appear in the Modules section.
Note that this will show the Module that the Procedures or Function resides it. It will not list the individual Procedures/Functions in the Object Explorer.
If you double-click it, it will open the Module and show you everything contained in it.

The VBA code should now be connected to the button that the macro was once connected to, so it does the same action when the button is clicked that the macro would have done.

The conversion of the Macro to VBA code is not automatically linked to anything - you need to assign it your button. All the converter does is take a macro, and create the VBA equivalent of it in a "standalone" module that is not linked to anything.

To assign it to a button:
- open the Form in Design View
- add the button to a Form (if it does not already exist)
- go to the Properties of the button
- go to the "On Click" event of that button (top listing on the Event tab) and on the right-side of that box, click on the button with the three dots in it
- select "Code Builder" (this will give you a shell of the On-Click event for your button)
- copy the VBA code created in the conversion (minus the first and last lines of the Function and any error handling) in between the two lines of the created shell
- Save

You now should have the code you need attached to the button.

[Lou_Reed]

Okay, these instructions tell you how to connecting up an embedded macro converted to VA code You simply find the button, go to its properties, go to the on click and look for the ellipsis and select codebuilder, etc.

That is straightforward. But what if it is not an embedded macro. There is o button with its properties easily available. What do you do then?

Any help appreciated Thanks in advance.

Respectfully,

Lou Reed

[JoeM]

No, my instructions do not reference connecting to an embedded macro. They tell you how to put VBA code in an Event of a button (like clicking on it).
Embedded macros are usually built through the Wizards. I hardly ever use them. I like to see my code plainly.

I am not sure I understand what it is that you are trying to do. Are you trying to connect your VBA to something other than a standard Command Button? Maybe some other button or type of object?

[Lou_Reed]

I am working on a db that was started by someone else and they have macros that do not seem to be triggered by a button click on a form.
I understand what you are talking about when there is a button that references or connects to the VBA code (with the first and last line of the SUB).
But some macros do not have a button that triggers them. They have something else. So what do you do when in that situation?

That is my question.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

In the two attached files one is the db that I am working on and the other is a screenshot of the macro FormHeading that was opened in design view. It is not connected to s button at all. I can convert the macro to a VBA code and I can save the new module. The question is how do I modify the db so that the VBA code runs in place of the macro FormHeading.

As far as I am concerned the macro code can be thrown away after that. I just was the get the VBA to run in place of the macro FormHeading. This macro is not connected to any button at all. It is connected some other way.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Upon an internet search I believe that my question is this: I have several standalone macros that I would like to convert to VBA code. I can do that. Since they are standalone macros, they are not associated with a button.

After, I click convert and answer all of the questions the macro to VBA conversion software asks, then what do I do to make the VBA work and to get rid of the old macro? I believe that I know how to do it for embedded macros that is easy that is attached to a button. The standalone macro is not associated with a button. It is stand alone.

However, the purpose of this exercise is to replace the macro with VBA code so that is what I want to do.

It is the last part that is confusing me.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[JoeM]

It all depends on how these old macros are being called to run.
When exactly do they run?

[Lou_Reed]

I put an answer to this last post about a hour ago. It must have either been taken down or not madea part of the thread. Anyway, I have the translated macro's VBA in the modules section of my db. In the only macro that I wrote which is "Open form AddPersonnel and form Personnel for Data Entry", It should be obvious what the macro does, but just in case by clicking on the named macro it opens up those two forms for data entry.

Now I want to throw away the macro created here and in the future run the VBA code that essentially does the same thing as the macro. How do I do that? This code is not attached to a button so that will not work.

How do I activate it?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[JoeM]

Ah, OK. I think I see what you want to do know. You want to run the VBA code you come up with from a Macro.
You can do that. Just keep the first line of the converted VBA code starting with "Function...". I would recommend changing the name so that it is named different from the original Macro name.
For this example, let's that the name (first line) of our converted code now looks like this:

Function MyCode()

Then, in a new Macro, select the RunCode action, and enter the name of your VBA code, like this: MyCode()

Now, when you click the Macro, it will run that code.

[Lou_Reed]

Okay, I see what you are talking about. As I said these are just standalone macros (most were not written by me). Some run from the internal operation of the db, but mine just runs when you click in the macro name. I want to run mine now translated into VBA code. I think you have answered the question. Just make a macro shell out of it.

Well that takes care of one macro translation.

I will start working on the others now.

Respectfully,

Lou Reed

[ssanfu]

<snip>.... But what if it is not an embedded macro. There is o button with its properties easily available. What do you do then?
Any help appreciated Thanks in advance.

In VBA code, you can execute a (standard) macro using the command

    DoCmd.RunMacro ("FormHeading")

Once you convert the macro to VBA code, you just call the sub.

So here is one example:
You convert the "FormHeading" macro to VBA. The VBA looks like

Option Compare Database
Option Explicit

'------------------------------------------------------------
' FormHeading
'
'------------------------------------------------------------
Function FormHeading()
On Error GoTo FormHeading_Err

    With CodeContextObject
        TempVars.Add "Heading", DLookup("[Heading]", "tblDatabaseConfig", "[ID] = 1")
        DoCmd.SetProperty "lblHeading", acPropertyCaption, TempVars("Heading") + " - " + .Caption
        TempVars.Remove "Heading"
    End With


FormHeading_Exit:
    Exit Function

FormHeading_Err:
    MsgBox Error$
    Resume FormHeading_Exit

End Function

Now, I would fix the conversion; I would change the code to

Option Compare Database
Option Explicit

'------------------------------------------------------------
' FormHeading
'------------------------------------------------------------
Function FormHeading()
    On Error GoTo FormHeading_Err

    Forms!DivisionDashboard.lblHeading.Caption = DLookup("[Heading]", "tblDatabaseConfig", "[ID] = 1")

FormHeading_Exit:
    Exit Function

FormHeading_Err:
    MsgBox Err.Number & " - " & Err.Description

End Function

In the code for the "DivisionDashboard" form, there is a "Form_Open" procedure. The procedure is

Private Sub Form_Open(Cancel As Integer)
    DoCmd.RunMacro ("FormHeading")
    Switchboard.Requery
    Update_Actions
End Sub

Since this code is calling a macro, you must edit the code to execute the VBA.
It would look like:

Private Sub Form_Open(Cancel As Integer)
    '    DoCmd.RunMacro ("FormHeading")
    Call FormHeading
    Switchboard.Requery
    Update_Actions
End Sub

Everyplace in the code, comment out the "DoCmd.RunMacro" command and add the "Call FormHeading" line.



I would also add a new Module named "ConvertedMacros" and move all of the converted macros VBA code to the new module, then delete all of the modules that have the name "Converted Macro- xxxxxxxxxxx".... except the module "Converted Macro- AutoExec". I would delete that one totally.
(the module and code, not the macro)

[Lou_Reed]

https://www.accessforums.net/showthread.php?t=66719

In the link above on Post # 12, I need to compile the code at step 11. It fails on the mcRestore Sub on


Reopen

sub or function not defined.


The attached files show the db in question, plus a screenshot of the error when it happens and where it happens. The Sub mcrRestore fails because Reopen is not defined.

This worked when mcrRestore was a macro. Now after making it a VBA SUB it fails on the line
Reopen not defined.

This VBA translation was automatic and I am not sure what the problem is.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed