Needs to be a proper design thread on here

[MARK858]

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]

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.

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]

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]

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]

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]

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]

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
- a good tool to get certain code snippets
- a satisfactory tool to automate some simple tasks

No one will dispute that (well written) VBA code is better than recorded code. All things equal, that is the preferable way to go. But not everyone here is a programmer or the desire to learn to code. And that is OK. We all have different skills and interests. That is what makes the world go round!

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]

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]

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
3 Education and Training
6 Family / Whanau Services
6 Basic Needs
6 Education and Training
15 Family / Whanau Services
15 Health

I would like to merge rows based on the Index number into Single Row as given below
3 Youth Services, Family / Whanau Services, Family Violence, Education and Training
6 Family / Whanau Services, Basic Needs
15 Family / Whanau Services, Health

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.