Question Converting Macros to VBA

[RexesOperator]

About one third of my command buttons use simple macros that open and close various forms and reports. The most complex one has 4 lines in it. The remainder required some programming control so I used VBA.

Should I bother converting the remaining Macros to VBA? This involves about 40 macros.

I am using Access 2003.

One constraining element is time - this project will be deployed on the network next week. I just want to know if it is worthwhile.

 

[Mike375]

If you have your DB as an MDE file it won't block entry to macros. That in fact is the reason I am using code instead of macros for actions that macros can do.

If you convert the conversion won't handle any macros that have a RunMacro action. Well it converts but the conversion has code for RunMacro so the macro being run is still required.

One possible reason to change them is some people might say the DB is not very good because it has macros.

 

[Pat Hartman]

There is no error trapping available with macros. If any macro generates a runtime error, it will drop the user into an ugly error dialog.

 

[boblarson]

There is no error trapping available with macros. If any macro generates a runtime error, it will drop the user into an ugly error dialog.

Unless you are using the new 2007 ACCDB/ACCDE format. Macros do have error handling (albeit not the greatest) available which can be helpful.

 

[Pat Hartman]

In A2007 macros have been given new life including error handling. In fact, the Access team is pushing them hard because of the security they've added to A2007 which is extremely annoying unless you use only "safe" macros.

 

[Mike375]

On A2003 there is the facility to convert macros to code on forms.

In Design Tools>Macro>Convert

I never had success with it. The form would lock up when I clicked bttons/labels but your mileage might be different.

 

[RexesOperator]

One possible reason to change them is some people might say the DB is not very good because it has macros.

Why does having macros mean the DB is not very good? Is this just a convention/preference?

Based on the discussions this topic generated I will convert the macros to VBA.

 

[boblarson]

Why does having macros mean the DB is not very good? Is this just a convention/preference?

Based on the discussions this topic generated I will convert the macros to VBA.

It's not that the db isn't good because it uses macros. There is a bit of bias out there that favors VBA over macros (I have to admit that I won't use macros at this point because I find them too limiting and due to a lack of error handling ability). But, if it works and it works well, there's nothing wrong with using macros. And now that there is error handling available, from within Access 2007 ACCDB/ACCDE/ACCDR files, the difference is even less evident.

 

[Mike375]

Why does having macros mean the DB is not very good? Is this just a convention/preference?

Based on the discussions this topic generated I will convert the macros to VBA.

Macros are looked down on in forum and the internet generally. So anyone using your DB who has a litte bit of internet knowledge........

Macros in my opinion are greatly underated. I recently had a situation where a number (it was a date X 86400) was entered on the OnClick line of a textbox as a hidden reference and if Date()*86400> than the number entered on the OnClick line..then do this....If Then End If would not read it but macro condition column did read it.

But the vast majority of people with any internet reading will look down on the macro.

I think the batch file falls into the same category but to a lesser extent. Although Shell can be extremely clumsy compared to a batch file to open files it has the advantage that you don't need to install a file on the persons computer.

 

[The_Doc_Man]

The problem with Macros is more generic than just Access. The Dept. of Defense had a "thing" about macros in any utility program. Excel, Word, Powerpoint, Access, and some non-MSO utilities. Too many vulnerabilities.

As a result of government complaints, settings were revealed that allowed managers to "ramp up" security to prevent use of unsigned macros. I think this setting was made the default case for WinXP (any variant) in SP2. It has been a while but that's what I remember from when I used to teach security to our system admins.

In any case, I'm of the opinion that EVERYTHING in the tool kit is fair game if you know how and when to use it. Macros make sense sometimes. Just not as often as VBA if anything really complex is being attempted.

 

[Mike375]

In any case, I'm of the opinion that EVERYTHING in the tool kit is fair game if you know how and when to use it.

Ditto. I am a real ***** with this stuff, absolutely no principle at all

[quoteMacros make sense sometimes. Just not as often as VBA if anything really complex is being attempted.[/quote]

Depends on what you mean ny complex. It can be simple but the avialble actions form a macro are not available.

But if you are talking about heaps of action lines, then no problem with a macro. I have some very big macros. For a coupe of parts of the process a macro action won't do so I have that in a module and RunCode for the macro action. Code can very messy trying to replicate some RunMacro actions.

The main negative I find with big macros is the lack of "word processing" that code has. In one of the actions lines you might have a typo on a field name so up pops the Halt but then you have to find the offending action line.

Not being able to block out macros with MDE, that is a recent one for me.

In the world of forums a disadvantage with macros is being unable to copy/paste to the posting.

 

[Pat Hartman]

If you compare the effort it takes to create a macro to the effort to create a line of VBA for the typical one-line property settings, the difference is miniuscule. In my mind, the downside of the macro is two-fold. First macros are isolated from the main body of the code so you can't view them with the code, and secondly in versions prior to A2007 there is no error handling availabe nor do the macros support variables.

I occassionally use macros for my internal use if I have a bunch of update queries I want to run without interaction. I would never use macros for anything that required logic because I think the logic required to create the macro is counter intutitive and in some cases down right convoluted.

 

[Mike375]

First macros are isolated from the main body of the code so you can't view them with the code,

For my use and telemarketing DBs in particular it is the stand alone apsect of the macro that is its main attraction. The ease of changing over the phone and while the DB is open. If it is a complicated macro that I would need to alter it can be emailed in a DB with an Autoexec. With code I would need to do the whole form and the form the person is using will have some differences to what I have.

I would never use macros for anything that required logic because I think the logic required to create the macro is counter intutitive and in some cases down right convoluted.

Again, horses for course I find that the definite "line by line" of the macro to be more logical.

 

[Simon_MT]

I would definitely convert Macros to VB. Ignoring the issue of error trapping, the great thing about VB is you can search through all the code, do find and/or replace.

I would go even further, take every bit on code off the forms and put them into VB as well. You end up with one depository for all your code, its totally reuseable and easy to manage. Personally, and not being disrespectful to the above, it was the best thing I ever did within the structure of the database. I wrote everything in Macros in 97, once I discovered With CodeContextObject the penny dropped.

Simon

 

[Mike375]

With CodeContextObject
Simon

I have seem that come up when some macros are converted. From memory it is when the macro has condtions Is Null or Is not Null

 

[Simon_MT]

If replaces Me in Modules attached to Forms or referencing the current object.

Its damn good!

Simon

 

[Mike375]

Here is one and as you can see it has been a SetValue macro for visible

With CodeContextObject
If (Eval("[Ph] Is Null")) Then
.CLSurname.Visible = False
End If
If (Eval("[CLPh] Is Null")) Then
.CLDOB.Visible = True
End If
End With

The macro has no reference to the form in either the condition column or the Expression/Item.

I thought it might have something to with the Is Null

 

[ChrisO]

Another aspect of preferring VBA over Macros that, to my knowledge, has not been mentioned...

Macros in Access are specific to Access.
VBA is used in many different applications not just Microsoft products.

If we need to switch from say Access to Rockwell Software products then Access Macros won’t cut it but, by and large, VBA will.

So Macros are, to a large degree, a dead-end street and adding error handling to them will not make the knowledge of them more portable between applications.

But a knowledge of VBA, with some minor twists, is portable between many different applications.

Regards,
Chris.