# Needs to be a proper design thread on here



## codeliftsleep (Dec 21, 2017)

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 (Dec 21, 2017)

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 (Dec 21, 2017)

> 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 (Dec 21, 2017)

footoo said:


> 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 (Dec 21, 2017)

codeliftsleep said:


> 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 (Dec 21, 2017)

footoo said:


> 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 (Dec 21, 2017)

codeliftsleep said:


> 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 (Dec 22, 2017)

Seems this thread is a bit of an echo 

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


----------



## codeliftsleep (Dec 22, 2017)

footoo said:


> 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 (Dec 22, 2017)

MARK858 said:


> 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.


----------



## MARK858 (Dec 22, 2017)

Must admit much as I don't like recorded code I feel it has it's place both as a tool to help inexperienced users get syntax (we can teach them how to speed it up) and for things like getting the correct character spaces when converting text files.

As for the spreadsheet design I too see a fair bit of poor design but as it stands the forum is a help forum and not really a spreadsheet design course (if people want this then they could do it via MrExcel consultancy which is a different matter).

You also need to realise that some posters don't have control of the layout as it is dictated by powers above.

BTW, be careful as the 2 threads could be interpreted as duplicate posts which is against the forum rules.


----------



## codeliftsleep (Dec 22, 2017)

MARK858 said:


> Must admit much as I don't like recorded code I feel it has it's place both as a tool to help inexperienced users get syntax (we can teach them how to speed it up) and for things like getting the correct character spaces when converting text files.
> 
> As for the spreadsheet design I too see a fair bit of poor design but as it stands the forum is a help forum and not really a spreadsheet design course (if people want this then they could do it via MrExcel consultancy which is a different matter).
> 
> ...



I'd argue that its not really helping them by telling them how to do things in jacked up ways that will simply cause them problems down the road and neccesitate a total redesign once they realize how broken it is instead of telling then they way they have it designed is completely wrong before they get too far into it.

As far as the duplicate post feel free to delete rhe one buried in the othee thread.


----------



## footoo (Dec 22, 2017)

codeliftsleep said:


> 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.



I think Joe4's post in the previous thread was an entirely adequate response to your post, and made this thread unnecessary.

Whether you like it or not, the macro recorder is a very useful tool - particularly for users with little or no knowledge of VBA - and MS should be applauded, not castigated, for providing such a tool.


----------



## codeliftsleep (Dec 22, 2017)

footoo said:


> I think Joe4's post in the previous thread was an entirely adequate response to your post, and made this thread unnecessary.
> 
> Whether you like it or not, the macro recorder is a very useful tool - particularly for users with little or no knowledge of VBA - and MS should be applauded, not castigated, for providing such a tool.



I never said it wasn't useful. I said it shouldnt be used in production code. Big difference.  

However, It also teaches people bad habits and allows them to use it as a crutch instead of learning how to do things properly, which is definitely not good, at least if you are building anything more than something simplistic that you need to use VBA for.


----------



## MARK858 (Dec 22, 2017)

codeliftsleep said:


> I'd argue that its not really helping them by telling them how to do things in jacked up ways that will simply cause them problems down the road and neccesitate a total redesign once they realize how broken it is instead of telling then they way they have it designed is completely wrong before they get too far into it.
> 
> As far as the duplicate post feel free to delete rhe one buried in the othee thread.



As I already stated spreadsheet control is not always down to the person posting here, if their boss insists they want it to look a certain way that is it.
I am lucky and mine will listen 98% of the time but there is the other 2%.

I personally will tell OP's if I think the design is really bad but I have no intention of giving them lectures on it.

As for deleting the other post that will be down to a Moderator to decide upon if they see both threads.


----------



## footoo (Dec 22, 2017)

codeliftsleep said:


> I never said it wasn't useful. I said it shouldnt be used in production code. Big difference.



I can't find any of that in your original post. But you did write : "a big part of the issue is that Microsoft allows people to record macros"


----------



## Joe4 (Dec 22, 2017)

This is what I said in the other thread:


> I think you are painting with too broad a brush here. Not every situation or person is the same. Not everyone here is a professional programmer, nor do many of them have any desire to be. Dare say, many would even qualify themselves as non-technical people. So to expect everyone here to learn VBA is really not all that feasible.
> 
> The Macro Recorder can be a:
> - stepping stone for those just learning to write code
> ...


What you are describing seems to be a theoretical utopia, a place where everyone has the desire, time, and ability to become a competent VBA programmer.  I would love to live in that world!  But sadly, no one does.

As was mentioned previously, if someone can record a macro that saves them a bunch of time, that is an improvement, and should not be easily discarded/discounted.
I have seen people go to the extremes on the other end to write code that improves code that already takes less than a second to run to save a few milliseconds.
While us programmers may get all "geeked up" about that, the user won't notice any difference.  And from a financial standpoint, it may be a bad business decision (how much time & money were spent improving a process that didn't really need improving).   In the "real world", the financial aspect needs to be considered (cost vs. benefit).


----------



## Joe4 (Dec 22, 2017)

> 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.


Already exists here: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

But of course, that depends on people actually reading the Forum announcements: https://www.mrexcel.com/forum/board-announcements/


----------



## footoo (Jan 18, 2018)

To illustrate how useful the macro recorder can be to someone who knows no VBA :

In this thread : https://www.mrexcel.com/forum/excel-questions/1039433-merge-rows.html
the original question was :


> I have a list of records
> 3    Youth Services
> 3    Family / Whanau Services
> 3    Family Violence
> ...



Let's assume the rows of data may vary each time but will always fall far short of 10,000 rows.
The following macro is from the macro recorder with no changes made to the code :

```
Sub Macro1()
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Range("C2:C10000").Select
Selection.FormulaR1C1 = "=IF(RC[-2]<>R[1]C[-2],0,""d"")"
Range("D2:D10000").Select
Selection.FormulaR1C1 = _
    "=IF(RC[-3]<>R[-1]C[-3],RC[-2],R[-1]C & "", "" & RC[-2])"
Columns("C:D").Select
Range("C2").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("C:C").Select
Range("C2").Activate
Selection.SpecialCells(xlCellTypeConstants, 2).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Columns("B:C").Select
Range("B2").Activate
Selection.Delete Shift:=xlToLeft
End Sub
```

This code might be considered inefficient, but the run time is a fraction of a second.
A considerable saving - without any VBA knowledge.


----------

