1 edit
Originally posted by andrew93In my experience people jump into learning VBA much quicker when you show them how the macro they recorded is 'put together' in code and how easy it actually is.
As a side note I recommend you don't use the macro recorder. It is only useful for very specific highly repetitive tasks - but as soon as the data varies in the tiniest way (an extra column for example) then the macro won't work. IMO it is better to learn VBA and code your macros from scratch so you don't have to re-write your macros when the data layout changes, or the sheet name changes etc.
Just telling someone - who possibly is not a very technical user - to just "learn VBA" is a little silly.
Originally posted by adam warlockJust get yourself some people. And then have your people deal with other people's people. Make sure one of your people is one of those people who know their way round Excel.
Does anyone know of any kind of good, free, online material that will allow me to update and increase my excel skills.
3 edits
Originally posted by CrowleyI disagree - I don't see any silliness in my suggestion at all. I have consulted to many who relied on the macro recorder, who then couldn't code their way out of trouble when the data changed in the most minuscule of ways - which it always does. IMO it's not a good way to learn because it teaches extremely inefficient methods. Rather than allow VBA to be a mystery it is better in the long run, and much more rewarding, to learn it properly.
In my experience people jump into learning VBA much quicker when you show them how the macro they recorded is 'put together' in code and how easy it actually is.
Just telling someone - who possibly is not a very technical user - to just "learn VBA" is a little silly.
But each to their own. For some people maybe the macro recorder is the easiest way (and like I said earlier it is ok for very specific highly repetitive tasks), but for others it may not be.
Originally posted by andrew93You're looking at this the wrong way.
I disagree - I don't see any silliness in my suggestion at all. I have consulted to many who relied on the macro recorder, who then couldn't code their way out of trouble when the data changed in the most minuscule of ways - which it always does. IMO it's not a good way to learn because it teaches extremely inefficient methods. Rather than allow VBA to b ...[text shortened]... id earlier it is ok for very specific highly repetitive tasks), but for others it may not be.
He wants to 'get better Excel skills', so he needs to follow certain steps to get there.
Then I suggest getting into VBA through the Macro Recorder.
I like to use Macro Recorder as a starting point. It's easy, simple and safe.
He doesn't want to learn programming, but Excel rather. So telling him to go and study a superset of Visual Basic is silly.
Originally posted by CrowleyI disagree - I believe you are taking my comments out of the original context and that we are pretty much saying the same thing regarding the starting point - being formulas first then macros later (it's just the macro path that differs). Per my first post I say it's better to not get into using macros - instead it is better to learn how to use Excel properly using formulas, quote:
You're looking at this the wrong way.
He wants to 'get better Excel skills', so he needs to follow certain steps to get there.
Then I suggest getting into VBA through the Macro Recorder.
I like to use Macro Recorder as a starting point. It's easy, simple and safe.
He doesn't want to learn programming, but Excel rather. So telling him to go and study a superset of Visual Basic is silly.
"BUT, there is usually a way you can solve (almost) any issue using a formula or two, without defaulting to using a macro. Learn the various formulas first and how they can be adapted and modified in such a way you wouldn't have thought possible."
I often see people jump into macros when a formula or method will perform the task just as well.
When I mentioned using formulas in a way you ("you" being the OP of this thread) wouldn't think possible (if one was a beginner), here are some examples that come to mind:
- using a formula to return multiple lookup values in a single cell - i.e. to return all instances of a lookup where there are a number of matching values
- copying data onto a new sheet, while re-sorting a list of text values into alphabetical order using just formulas
- using a single formula to calculate a weighted average
- using a single formula in a sumif/countif situation where there are multiple conditions, without using helper cells
- finding the last row used in a sheet using a formula
- finding the nth instance (e.g. the 3rd of 5 matches) of a value in a list using a single formula
- retrieving a value from a table using a single formula where you require a combination of the hlookup and vlookup functions - neither will suffice because you don't know how far down the list, or across the columns, you need to go.
There are plenty more example, but what this illustrates is that (IMO) you are better off learning the power of Excel formulas first - which I'm sure you agree with based on your posts.
For people who pursue an interest in Excel, I find the developmental path is : formulas > macro recorder > VBA programming > smarter formulas.
So my earlier post was coming from the angle of why go through the 2 middle steps when you can jump to the end? This includes skipping using the macro recorder (on which we will have to agree to disagree) but I'm going one step further and saying skip macros unless there is absolutely no way of doing something (within reason) without using a formula.
Cheers, Andrew
Originally posted by andrew93Oh yes.
There are plenty more example, but what this illustrates is that (IMO) you are better off learning the power of Excel formulas first - which I'm sure you agree with based on your posts.
For people who pursue an interest in Excel, I find the developmental path is : formulas > macro recorder > VBA programming > smarter formulas.
So my earlier post was c ...[text shortened]... bsolutely no way of doing something (within reason) without using a formula.
Cheers, Andrew
I find myself pressing Alt+F11 when I come across a problem instead of thinking of ways to string functions together. But I came at Excel from a developer background, so I may be excused 😉
I still think the Macro recorder is invaluable though. I understand your point, but like you said - agree to disagree.
For repetitive formatting tasks - Macros are perfect and can be reused.
For problem solving - native functions, then VBA.
