Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Posts
    66

    Unanswered: Use of Macros - what do people think?

    Hi

    I'm slap bang in the middle of putting together what's turning out to be a pretty big database with in or around 48 forms/subforms, 34 tables/temp tables, and 84 queries of all types.
    Thing is I've little or no VB coding experience, so everything's hanging together mostly on Macros, and maybe only 20% based on code I've written (all be it poorly).
    Last count there were almost 200 macros - and there'll be more to come once I start producing reports (up to 20 needed).

    Does this use of macros mean the DB is fundamentally flawed? Do they slow down performance? Does it make the DB look amateurish?
    Looking back now, I'm starting to think I should have spent more time learning basic VB before starting on this...

    What do people think about use of Macros as opposed to code in Access?

    D

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Use of Macros - what do people think?

    Good Morning Dave...

    I wouldn't say your database was flawed by having macros... I would have suggested you creat Macro Groups rather than 200 individual ones... but that's a personal choice... for organization purposes...

    Here's a link that explains the differences between Macros and VBA coding MUCH better than I could... It's old, but the information still applies... http://www.microsoft.com/AccessDev/A...Web.htm#SHOULD

    Personally I think it's just a question of how complicated of a process the database application is doing... I have many databases that have Macros and Macro Groups in them... but I can't say that I've used them in any of my recent projects...

  3. #3
    Join Date
    Jan 2004
    Posts
    75
    Another option for the beginner.... once your forms are built, go back into the design view of the form. On the menu bar, choose Tools, Macro, Convert Macros to Visual Basic

    Once you've completed this, you can then delete your macros...

    Not only do you clean up your database, you can then see how you perform those tasks in VB... thus learning code as you go...

    I did quite a bit of that myself when I first got started (not that I'm a pro... still hangin' on to that amateur status)

  4. #4
    Join Date
    Dec 2003
    Location
    New jersey, USA
    Posts
    9
    I am a progressing novice as well, but have been where you're at not too long ago, so here's my insight based on my experiences.

    Depending upon what you are trying to accomplish, macros can offer all the flexibility you need or not enough of the functionality you need to accomplish your task. I know this is vague, but let's say you need to have a command button on your form that will open another form based on criteria on the form you already have opened. A macro or VBA code can handle the parameters you need to accomplish this.

    If you have a different task that requires decision making, a macro may not have the options you need, so you will have no choice other than to use VBA.

    Aside from examples in books and this forum I learned VBA by creating "dummy" macros for each macro command, and then doing "Convert Macros to Visual Basic" from the "Tools" dropdown, "Macros" option. You can then use that info in other VBA you may need to create. This is the same thing Sublimsos suggested, and it really is helpful. Trudi's suggestion of creating macro groups help keep your macros organized.

    Given that the end result is the same I will always use macros because they are easier to understand by a greater audience. I disagree with anyone considering them amateurish. In my opinion "effectiveness" is paramount. Sometimes that means "APPEARING amateurish" using macros that everyone can understand verses VBA that only a few can.

    I have managed to learn VBA on an "as needed" basis. Most of the books I reference break down VBA code by functionality. The forms chapter contains VBA examples relative to tasks done by forms. I found the chapters I've referenced the most are those on dealing with recordsets. Generally, once you have the correct data you need for your form or report, the "control source" property can take care of mapping the fields.

    I am learning to break the mold of textbook approaches, if breaking the rules make my application more efficient. Use common sense and look at the big picture.

    Hope this helps. Good Luck.

    Scott

  5. #5
    Join Date
    Nov 2003
    Posts
    66
    Appreciate the input, folks - kinda put my mind at ease.
    I suppose my main concern is how the DB 'looks' to someone else who may have to come along years down the road to expand the functionality. I've found the macros to be effective and relatively easy to create, tho as you say, Scott, they can't do everything, hence the dabbling in actual coding.
    This DB is a redevelopment of one created years ago based almost entirely on VB and I reckon (so far) it compares pretty well.

    Still tho, gonna try the 'convert to VB' for a few forms and have a look at the results. I had no idea this was possible.

    Thanks again for your time - much appreciated.

    Dave

  6. #6
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    Using macros is not amateruish. In fact it is a good way for many people to learn about relational databases and programming who might not otherwise get the chance.

    The more macros (or macro lines) you have the harder it will be to revise things later. If you have 50 macro lines it may not be hard but if you have 500 it can get exponentially harder.

    Consider:
    Next year management decides the FoozyWazzit process is now going to be called the WazzyFoozit and besides the obvious changes they want your error checking done in reverse order to better reflect the change.

    Or maybe you simply need to change a table or a field name.

    With VBA you could just do a find/replace in the code. With macros you have to open each one and hunt and peck, and then chances are you'll have missed something

    Speaking of error checking, you can do more of it much more easily with VBA

    As far as someone coming along later, consider the business you are in. Is it likely that the person after you will be a programmer or computer type person who may not have much knowledge of the market or product? Or is it likely that so much product knowledge is needed that they will have someone with deep knowledge of the business but not much programming knowledge? For the first VBA might be easier, for the latter macros in many cases would be easier for the next person to learn.

    I would caution against converting all macros to VBA in a mass update if your database is mission critical. Are you sure you'll understand the VBA enough to debug a serious problem tomorrow? If it's an essential part of your business I suggest you try to build new things and revisions in VBA instead of a new macro. And you can try to change one macro per day or per week to VBA. This will help you learn as you go and be up to speed enough on all your code should a major problem develop. One idea here migh be to make a copy of your database mdb and run the "convert macros to VBA" on that version, and use that as a learning environment rather than doing the onversion on your production copy.

    Mark
    Last edited by mdr02125; 01-22-04 at 13:49.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Use of Macros - what do people think?

    Originally posted by Trudi
    Good Morning Dave...

    I wouldn't say your database was flawed by having macros... I would have suggested you creat Macro Groups rather than 200 individual ones... but that's a personal choice... for organization purposes...

    Here's a link that explains the differences between Macros and VBA coding MUCH better than I could... It's old, but the information still applies... http://www.microsoft.com/AccessDev/A...Web.htm#SHOULD

    Personally I think it's just a question of how complicated of a process the database application is doing... I have many databases that have Macros and Macro Groups in them... but I can't say that I've used them in any of my recent projects...
    ... And I have never used them. I write everything in VBA/VB code ... I guess I must be experienced then ... Hmmmm. Hey Trudi, that was a nifty trick with the Select Case ... Using the True logical ... It's been awhile since I've seen it. Thanks.

  8. #8
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Use of Macros - what do people think?

    Originally posted by M Owen
    ... And I have never used them. I write everything in VBA/VB code ... I guess I must be experienced then ... Hmmmm. Hey Trudi, that was a nifty trick with the Select Case ... Using the True logical ... It's been awhile since I've seen it. Thanks.
    M Owen... Glad I could help!

    Daver... ... If your main concern is a neat and organized looking db.. I really would suggest you try using macro groups... I have one database that I made a long time ago... and there are only about 5 macros showing in the database window... mcrDailyGroup, mcrWeeklyGroup, mcrMonthlyGroup, mcrQuarterlyGroup... Inside each of these macros I've split the processes into separate sections with more descriptive names.... like "mcrExportDTR" etc... Then when you call or assign the macro you simply use "mcrDailyGroup.mcrExportDTR" ... It's great for organization...

    That's my 2 cents...

  9. #9
    Join Date
    Nov 2003
    Posts
    66
    Mark - I've already seen what you mean about converting too many macros at once - tried it on one fairly complex form in a copy of my DB and came up with a ton of errors when trying to launch the converted form - much head scratching followed. Reckon the way to go is bit by bit starting with the more basic 'open this form, and close that one' macros one at a time...
    It also looks like the VB conversion seems to throw in loads of code that isn't necessarilly needed for what I'm trying to achieve in each macro. Would this be true? I found similiar problems years ago in other MS apps such as Frontpage where the HTML behind what you drag and drop contained loads of garbage code that I just ended up stripping out.

    Trudi, had a quick look at the Macro grouping idea you suggested - the Help file only really explains how to use these groups when you create a new group and then go on to create new macros you're gonna put in it. Doesn't really explain how to group existing macro's into a new group retrospectively. Still think it's a good idea tho, and it would definately make the DB design much neater. Looking forward to the headache of trawling thru my 200 macros trying to organize 'em. Being a novice, I didn't really use particularly good naming conventions when creating them (ok - I didn't use naming conventions AT ALL ) so it's gonna be a rollercoaster ride of confusion..!

    M Owen -
    >>>>... And I have never used them. I write everything in VBA/VB code ... I guess I must be experienced then ... <<<<

    Ya big show off!!!

    Dave

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    M Owen -
    >>>>... And I have never used them. I write everything in VBA/VB code ... I guess I must be experienced then ... <<<<

    Ya big show off!!!
    Guilty as charged ... Really tho, you should be able to do in code everything that a macro can and then some ... You can put this code into functions in a module then they are exposed to all forms in the DB (unless made explicitly private) ...

    P.S. - The real fun is in having reports retrieve the info to be displayed in them thru code ... Kinda poetic.:;

  11. #11
    Join Date
    Nov 2003
    Posts
    66

    Penny Dropping....

    >>>You can put this code into functions in a module then they are exposed to all forms in the DB (unless made explicitly private) ...



    Ah..... So that's what 'Private Sub' means as opposed to 'Sub'????

    (indication of the level I'm at in terms of coding)

    Dave

  12. #12
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by M Owen
    Guilty as charged ... Really tho, you should be able to do in code everything that a macro can and then some ... You can put this code into functions in a module then they are exposed to all forms in the DB (unless made explicitly private) ...

    P.S. - The real fun is in having reports retrieve the info to be displayed in them thru code ... Kinda poetic.:;
    Poetic, yes! ... And you definitely can do much more with code... but nobody starts at the full out coding level... Let's support our friend Dave and give him time learn... lol...

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by Trudi
    Poetic, yes! ... And you definitely can do much more with code... but nobody starts at the full out coding level... Let's support our friend Dave and give him time learn... lol...
    Trudi,

    OKAY ... It does help to have a BSCS and 10+ years experience developing applications to do what I do ... I am duly chastened. So Dave, good luck to you and if you need some code pointers don't hesitate to ask on this forum ...

    - Mike

Posting Permissions

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