Needs to be a proper design thread on here

[codeliftsleep]

Hey all,

I'd just thought I'd chime in with my opinion after reading and attempting to help some posters over the last few days. I've been on the board here and there in the past asking questions on things I havent been able to find on Google related to VBA mainly.

My opinion, from what I've seen is that many of the solutions are poorly implemented. Columns with the same names, cells containing numerous values separated by commas, but most of all just things that dont make logical sense from a programmatic and design standpoint.

I use Excel a lot at work due to working for a bank. Banks love Excel, but they love it a little too much. They try and use it for everything. I am a programmer by trade that was brought in to properly design and automate various reports and to build standalone systems/applications that could be used to streamline various parts of the business.

Proper design is EVERYTHING when building solutions. In my opinion, a big part of the issue is that Microsoft allows people to record macros which are terribly inefficient and trains people to program poorly or not learn at all and simply rely on macros they record. Let me give an example. When I first started working at my job, one of the first projects was to automate a daily report that was taking 3 to 4 hours to do manually. There was a huge section of code that did a lot of copying and pasting and math between sheets and cells. This part of the code took 30-40 minutes to run. When I looked ar why it was slow, I saw about 700 lines of code which obviously was a macro that was recorded and not changes at all.

After completely re-writing this section from scratch I cut the code in half and it finished in under a minute. In fact the entire report now runs in under 15 minutes a day now and that is with 2 new sections added that werent there originally.

I say this not to brag but to show how terrible the recorded macros are if you are using them as is-which from many of the code samples I see on here, is the case.

My first piece of advice would be if you see this, run as fast as possible away from this solution. My second piece of advice is to stop using this as a crutch and take time and learn how to program VBA properly. VBA is a very easy language to learn, by far the easiest of the languages I use regularly(C#, VB.net, C++, PHP, VBA and javascript).

You may say this is going to take too much time that you dont have. Well, i would counter by saying that the amount of time you spend waiting on your code to run using macros that have been recorded will give you plenty of time to learn the proper way and start using it. You will end up spending time one way or the other...better you take that time and learn how to use it to save a lot of time in the future.

Either way, I'd advise the mods to create a proper design thread so people can post what they are trying to do and people can help them to actually design things to create a solid foundation to build off of rather than building a house of cards that you realize will not work properly after you've built it to a certain point.

 

[mikerickson]

I don't really disagree with anything you said.
But you should remember that the user (with what ever knowledge, ignorance, bad habits and hair in their ears they may have) is king.

Record Macro is a good feature because it help ignorant users use the Macro features.
(Recorded macros aren't bad macros, they just record everything that the user does. The reason that they over-select is because they aren't writing code, they are recording exactly what the user does, which requires selection since it is done through the user interface).

If someone is using Excel to balance their checkbook, its unreasonable to ask them to learn programming and proper design. Excel doesn't, it just give them a way to balance their checkbook, even if it is inefficient, the checkbook is balanced.

Excel is a fine tool, but developers need to remember that they are the user's servant, not their master. The user is king and Excel allows the king to do even foolish things, because the king decides what to do not the servant.

And kings who want to be good with spreadsheets would be wise to learn programming before delving deep into macros. (And professional Excel techs should be interviewed for their position by programmers rather than by accountants or HR specialists.)

 

[footoo]

When I first started working at my job, one of the first projects was to automate a daily report that was taking 3 to 4 hours to do manually. There was a huge section of code that did a lot of copying and pasting and math between sheets and cells. This part of the code took 30-40 minutes to run. When I looked ar why it was slow, I saw about 700 lines of code which obviously was a macro that was recorded and not changes at all.

30-40 minutes versus 3 to 4 hours - big improvement to a user without any VBA knowledge (and user can be doing something else during the 30-40 minutes).

 

[codeliftsleep]

30-40 minutes versus 3 to 4 hours - big improvement to a user without any VBA knowledge (and user can be doing something else during the 30-40 minutes).

No, the 30 to 40 minutes was one section of the 3-4 hours.

The code I wrote ran in 1 minute or less for that section and in total 15 minutes for the entire report, and you couldn't do anything on the computer while it was running because it ate up all the processing power.

 

[footoo]

No, the 30 to 40 minutes was one section of the 3-4 hours.

The code I wrote ran in 1 minute or less for that section and in total 15 minutes for the entire report, and you couldn't do anything on the computer while it was running because it ate up all the processing power.

But the point is that to a user with no knowledge of VBA, the macro recorder can save the user a lot of time.

I didn't suggest the computer could be used for something during the 30-40 minutes - I wrote that the user could do something else (even if it's a visit to the bathroom, or a chat with friends).

 

[codeliftsleep]

But the point is that to a user with no knowledge of VBA, the macro recorder can save the user a lot of time.

I didn't suggest the computer could be used for something during the 30-40 minutes - I wrote that the user could do something else (even if it's a visit to the bathroom, or a chat with friends).

Or in many cases be unproductive at work and have to stay late to complete all the rest of the things they have to get done that day while they are sitting there looking at their phone or talking to coworkers waiting for poorly written/recorded macros to complete.

 

[footoo]

Or in many cases be unproductive at work and have to stay late to complete all the rest of the things they have to get done that day while they are sitting there looking at their phone or talking to coworkers waiting for poorly written/recorded macros to complete.

I don't understand your point.
If something takes 3 to 4 hours manually and takes less time with a recorded macro, is that not good?

 

[MARK858]

Seems this thread is a bit of an echo

https://www.mrexcel.com/forum/excel...ds-abbreviations-post4973787.html#post4973787

 

[codeliftsleep]

I don't understand your point.
If something takes 3 to 4 hours manually and takes less time with a recorded macro, is that not good?

Let me explain a little better. The manual process I was referring to needed you to do a bunch of manual steps and then press a button which ran the macro I was referring to and then continue with a bunch of more manual steps. All this total took 3-4 hours.

The 30-40 minute macro was included in this time, it was not separate.

 

[codeliftsleep]

Seems this thread is a bit of an echo

https://www.mrexcel.com/forum/excel...ds-abbreviations-post4973787.html#post4973787

Yeah it was because it needed its own thread, not to get buried in a different thread.

That was kinda of the last straw post I came across that caused me to write this.