Results 1 to 14 of 14
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Macro to Module to Code (2000)

    My application was "born" probably (?) about eight years ago, and has had maybe about as many people messing around with it since then. I doubt much of the original exits today, and none of the data, but am aware that none of the "creators" (myself included) knew anything about code (Wizard wizards, if you know what I mean). I converted to A2000 last year and, due to all you good folks, much has been improved. However, I have noticed (from having run Performance Analyzer) that there are several instances of failing to have Option Explicit statements (now the default), multiple macros where code could be used (some converted to modules - some not), and various other areas needing improvement. Wheeeew, OK now here's the question:

    What benefit is there to converting macros to modules if the macros must remain for the (we'll say cmdbutton) to function? I thought that, perhaps, once the conversion had been accomplished, the macro could be deleted (Access Magic?). Take for instance the following module (converted macro). I deleted the macro and the cmdbutton failed (no magic in that).

    '------------------------------------------------------------
    ' mcrCloseCarriersOpenAddCarriers
    '
    '------------------------------------------------------------
    Function mcrCloseCarriersOpenAddCarriers()
    On Error GoTo mcrCloseCarriersOpenAddCarriers_Err

    DoCmd.Close acForm, "frmCarriers"
    DoCmd.OpenForm "frmCarriersAdd", acNormal, "", "", , acNormal


    mcrCloseCarriersOpenAddCarriers_Exit:
    Exit Function

    mcrCloseCarriersOpenAddCarriers_Err:
    MsgBox Error$
    Resume mcrCloseCarriersOpenAddCarriers_Exit

    End Function

    I then wrote the code behind the cmdbutton as follows (copying from the module - somewhat):

    Private Sub CmdAddNewCarrier_Click()
    On Error GoTo Err_CmdAddNewCarrier_Click

    DoCmd.Close acForm, "frmCarriers"
    DoCmd.OpenForm "frmCarriersAdd", acNormal, "", "", , acNormal

    Exit_CmdAddNewCarrier_Click:
    Exit Sub

    Err_CmdAddNewCarrier_Click:
    MsgBox Err.Description
    Resume Exit_CmdAddNewCarrier_Click
    End Sub

    Viola!! It works! So why would anyone bother to convert from macros to modules? Or, is there something I'm missing (like "pasting" a module to a cmdbutton)? Or, is Access actually running the module "on click" rather than the macro (and I'm just oblivious to it - along with most everything else)? There are thirty-seven of these little ######s in this application and, while I could certainly benefit from re-writing each and every one of them, would the application gain any benefit from my efforts? There's also the issue of FrontEnd/BackEnd, but I'll save that for another time.

    Any input, comments, criticisms, greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Module to Code (2000)

    Macros can only be used for one thing, while code is far more flexible. If you stick a macro into the event property of a control, then that macro will always fire when you trigger the event. If you convert the macro to code, you have to stick the function call into the event property instead of the macro. Code will accept arguments to tell it what to do under a particular set of circumstances. So if you might call a function from various places, you could pass it the control itself as an argument so it knows exactly what it should do. You can't do anything like that with a macro.
    Charlotte

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Macro to Module to Code (2000)

    O.K. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

    So, why convert to modules? The module looks no different to me than the macro (except that I can see the code). Is Access running the module, or the macro after conversion?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Macro to Module to Code (2000)

    One of the major reasons to convert macros to code (or not to use macros at all) is the error checking that goes with code. You have no error checking with macros.
    AFAIK I believe that you cannot set breakpoints in macros, although I may be corrected on this.

    Pat

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Module to Code (2000)

    No, you're right, Pat. You can't set breakpoints on macros, although you can step through them after a fashion.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to Module to Code (2000)

    The code looks the same because all you did was convert the macro to equivalent code. The purpose is to get away from macros, which are primitive, single purpose, and impossible to error trap. If you insert the macro into the control's event property, then it's the macro that runs. If you point to the function, the function will run. They can even have the same name, although you would refer to them a little differently in the property sheet. You refer to a macro by its name, but you refer to a function like this: =MyFunction(). YOu need both the "=" and the closing parens when you use a function.
    Charlotte

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Macro to Module to Code (2000)

    Pat,

    Thanks for your reply. I do understand the benefit of error handling in code. I just don't get what converting macros to modules accomplishes.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Macro to Module to Code (2000)

    Charlotte,

    I'm thinking perhaps I didn't structure my question correctly.

    Right click Macro, save as module. What, if anything, does that accomplish?
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Module to Code (2000)

    Saving a macro as a module does just that, no more and no less: it creates a VBA module that contains a procedure that has the same result as the macro. Optionally, it adds error handling (which doesn't exist in macros), and a few comment lines.

    It doesn't update event handlers that call the macro to call an event procedure instead - you have to do that yourself.

    Although you could leave the procedure as it was converted, it is worthwile investigating the code to see if it can be improved, expanded etc., since VBA can do a lot more than macros.

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Macro to Module to Code (2000)

    Thanks! Short, clear, and to the point. I suppose the next question would be a philosophical one; like why would MS offer the option of converting macros to modules if it accomplished nothing more than revealing the code; but, I'm not going to go there, not ever. I think re-writing the code would be a worthwhile exercise for me, and perhaps even an improvement in the efficiency of the application. As per the feedback, I see that macros may be minimally effective, yet far from efficient. Armed with that awareness, I'm off to practice code. Hope I don't do too much damage.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Module to Code (2000)

    Bryan,

    You don't have to convert all your macros to code.

    If you have short macros performing one or two actions, the equivalent code won't be much more efficient, if at all. I would say leave them alone, unless you want to do it as an exercise.

    Some macros shouldn't be converted at all: AutoKeys sets database-wide keyboard shortcuts, and AutoExec is run automatically when the database is opened (the latter can be replaced by code in the OnLoad event of the startup form, but there is no AutoExec procedure or something like that)

    On the other hand, if you have complicated macros with lots of conditions, they can often be rewritten in code to be more readable and more efficient. For example, macros have no equivalent of the Select Case ... End Select block:

    Select Case [txtState]
    Case "California"
    ...
    Case "Oregon"
    ...
    Case "Washington"
    ...
    Case "Nevada"
    ...
    Case Else
    ...
    End Select

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Macro to Module to Code (2000)

    Hmmm, something just occurred to me (Oh my god, I think it's a thought!).

    If I convert all these macros (37) to code, how can I be certain I have done it in every case where the macro was called? Is there some way of revealing where they are called from?

    Edit:
    (Ooops. I think I just stepped on your reply.)
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Module to Code (2000)

    You could use a global search utility to find (and if desired, replace) all occurrences of the name of a macro in your database. Two such utilities are Find and Replace from Rick Fisher (you can download a free evaluation version) and the more expensive SpeedFerret from Black Moshannon Systems.

  14. #14
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Macro to Module to Code (2000)

    Re: Find and Replace
    Wow. That's too cool! Thanks for the link.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •