Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Are the Macros in use?

    I am developing onto an existing database. There are a large number of macros, some of which seem to be duplicated.

    Is there a way of finding out if any controls are actually using these macros? As I would like to delete them to tidy things up if possible.

  2. #2
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Re: Are the Macros in use?

    Originally posted by HelpMePlease
    I am developing onto an existing database. There are a large number of macros, some of which seem to be duplicated.

    Is there a way of finding out if any controls are actually using these macros? As I would like to delete them to tidy things up if possible.
    If you're familiar with the basics of VB, you could do the following :
    in each form, choose the command <Tools><Macro><Convert Form's Macro's to Visual Basic>. This will rewrite all macro's used in the form into VB-code and the macro will no longer be used.
    After doing this for all forms, you can delete all the macro's since none of them should still be in use.

    If you're not confident enough in VB, try the following :
    Make a backup copy of the database. Delete all macro's in the original one. Open the original database and start working with it. Every time, Access needs a particular macro, it will prompt you for it as it will not find it. You can then import the macro from your backup copy. Like this, you will have after a time all the relevant macro's.
    Make sure to keep the backup copy until you are certain you'll need no more macro's for importing.

    Good Luck

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Perfect - I converted the macros to vb - all seems to be hunky dory at the moment

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by HelpMePlease
    Perfect - I converted the macros to vb - all seems to be hunky dory at the moment
    of course you've now got all the macros repeated in vb code i'm affraid that the only way i know to do it properly is the manual one
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    How do mean they are repeated?

    I have deleted all the macros.!!

  6. #6
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133
    Originally posted by HelpMePlease
    How do mean they are repeated?

    I have deleted all the macros.!!
    I suppose m.timoney suggests that my solution will create "double" code : several parts of code that could be replaced by one part.
    He's correct there but the solution he is suggesting is probably not what you are looking for : you should practically need to rewrite the whole database and we don't want that, do we?
    So relax : you have converted only the used macro's and by deleting them all, unnecessary macro's have been removed.

    Of course, if you want to improve the database, you can look into the code and see if you can improve it by using functions, variables, etc. These things will normally not been used since the code was converted from a macro (where you can't use things like variables that easy)

    My advise : keep the database as it is but when you have the time, check the code and try to improve it. This is how I got to learn VBA 7 years ago : don't steal another one's work but nobody can prevent you to learn from it!!

  7. #7
    Join Date
    May 2002
    Location
    London
    Posts
    87
    If you want to find out if the procedures you generated by converting the Macros to code are not used you may find it worth writing a quick sub to search all the modules for the name of a given procedure.

    I have some routines for this purpose (searching all modules or query SQL for a given string) but I need to get them off my backup at home and update them to use the new AllForms, AllModules collections.

    Or for the manual version just seach form the code window for the name.

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by Herman
    I suppose m.timoney suggests that my solution will create "double" code : several parts of code that could be replaced by one part.
    He's correct there but the solution he is suggesting is probably not what you are looking for : you should practically need to rewrite the whole database and we don't want that, do we?
    So relax : you have converted only the used macro's and by deleting them all, unnecessary macro's have been removed.

    Of course, if you want to improve the database, you can look into the code and see if you can improve it by using functions, variables, etc. These things will normally not been used since the code was converted from a macro (where you can't use things like variables that easy)

    My advise : keep the database as it is but when you have the time, check the code and try to improve it. This is how I got to learn VBA 7 years ago : don't steal another one's work but nobody can prevent you to learn from it!!
    Sorry poor wording on my part, what your saying is true but i was thinking more along that lines that by converting the macros it changed all macros (including the unused ones) into VB subroutines meaning that nothing has really changed he still has all of the usless marcos that were hanging around unused but now they're VB subroutines
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    m.timoney is right - now the user has to go through x amount of lines of code to determine as he stated "which controls are using these macros".

    One benefit of the code conversion is error handlling , but if the macros gave the intended or desired outcome for the user - I guess the macros were doing their intended tasks and the controls (I assume he means data fields or property setings for form controls) were affected as intended.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Instead of deleting your macros and reimporting them from a backup when needed, I just rename objects I don't think are being used by prepending "X" to their name. That way they sort out at the bottom so they are out of the way, and I can easily restore their prior names if they are needed.

    Regarding converting all the macros to VB code, which was a new one on me and looked nifty, how would it handle a macro which references another macro or references vb code that calls a macro. IE, is the conversion from macros to VB fully recursive?

    Another solution is to have each of your suspect macros insert a record into an audit table upon completion. That makes it easy to identify which ones are being used.

    blindman

  11. #11
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    That is the worst nightmare in Access - Macros calling macros

    My take is this: If the Macro does not call a macro - convert it to code and run the code instead and as blindman says - rename the macro


    if the macro calls a macro and you convert to code you will most probably have to store the converted code of the called macro within the same module as the first converted macro, and so on and so on - this could create some work.

    Chances are if the "developer" used alot of macros they were not that experienced and did what was necessary to complete the project with desired results. Your quest to do the application correctly is admirable and so the first thing I would do is make sure all the macros do function properly, and then as you build onto the app - use vba.
    if the macro does not work create the fix in VBA.

    As time permits slowly migrate the macros to code.

    Also do this very cryptically so they feel they cannot replace you (kidding)

    good luck

  12. #12
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Wink

    Originally posted by m.timoney
    Sorry poor wording on my part, what your saying is true but i was thinking more along that lines that by converting the macros it changed all macros (including the unused ones) into VB subroutines meaning that nothing has really changed he still has all of the usless marcos that were hanging around unused but now they're VB subroutines
    I'm sorry timoney, but I must disagree with you : converting macro's to VB with the Access command you find in each form will NOT convert unused macro's, only those macro's that are used in that form will be converted.

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by axsprog
    m.timoney is right - now the user has to go through x amount of lines of code to determine as he stated "which controls are using these macros".

    One benefit of the code conversion is error handlling , but if the macros gave the intended or desired outcome for the user - I guess the macros were doing their intended tasks and the controls (I assume he means data fields or property setings for form controls) were affected as intended.

    you've got to be kidding the default error handling is worse than no error handling (atleast then you can see where the error occurs by hitting debug)
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by Herman
    I'm sorry timoney, but I must disagree with you : converting macro's to VB with the Access command you find in each form will NOT convert unused macro's, only those macro's that are used in that form will be converted.
    ah yes i'd forgotten about the other method of converting macros my appologies, that method will make duplicates of every common macro in every control that uses it which makes maintaining them an extrmly time consuming and labourious job

    which is why it can be even worse than the other method for removing redundent code
    Last edited by m.timoney; 07-11-03 at 07:07.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    we are side stepping the best solution for the user

    if the macro does not call functions or other macros then the user "should" convert to code one macro at a time

    the three reasons are:

    1) Error handling
    2)learn more about development
    3)if they leave their position, the next one to inherit the app will not be posting the same issue

    I agre with m.timoney - convert and learn, and if the code runs - do a project wide search in code to see if the macro is called up any where else and insert the code - if the macro is called from other macros - leave it alone for now

    Do these conversions in "phase" technology - one step at a time.

Posting Permissions

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