# Is anybody checking my work?



## Superstar31 (Feb 11, 2014)

So I've been busying trying to sharpen my basic excel skills by answering basic excel questions on this site.... with that being said... As great as i think i am, it would be nice to know if there are better ways to answering some of the questions I've answered (minus macros... I hate macros) 

So.... is any admin or other qualified person making sure i've not giving all these people horrible wrong advice?

I just realized I probably posted this in the wrong section.. whoops!


----------



## barry houdini (Feb 11, 2014)

Superstar31 said:


> So.... is any admin or other qualified person making sure i've not giving all these people horrible wrong advice?



Hello Superstar31 (whatever happened to Superstars 1 to 30?)

I haven't been around here too much recently so perhaps I'm not best qualified to comment on your specific ouevre......but in general these things are normally "self-corrected" by the contributors here - you wouldn't manage 300 posts of "wrong advice" without some other posters jumping in and correcting you....so unless that's happening on a regular basis I think you can assume that you're doing a good job 

Keep on "excelling"!



Superstar31 said:


> I just realized I probably posted this in the wrong section.. whoops!



Moved to the lounge...


----------



## AlphaFrog (Feb 11, 2014)

Also, you can see the number of views a thread has in the forum thread list. I would guess several of those views are from experienced contributors and MVPs. Someone will surly point out any gross error.

Keep up the good work.


----------



## Superstar31 (Feb 11, 2014)

barry houdini said:


> Hello Superstar31 (whatever happened to Superstars 1 to 30?)
> 
> I haven't been around here too much recently so perhaps I'm not best qualified to comment on your specific ouevre......but in general these things are normally "self-corrected" by the contributors here - you wouldn't manage 300 posts of "wrong advice" without some other posters jumping in and correcting you....so unless that's happening on a regular basis I think you can assume that you're doing a good job
> 
> Keep on "excelling"!


All I'm going to say is... There can be only 1... lol

And you'd be suprised how many post you can have if you just say it with CONFIDENCE!





AlphaFrog said:


> Also, you can see the number of views a thread has in the forum thread list. I would guess several of those views are from experienced contributors and MVPs. Someone will surly point out any gross error.
> 
> Keep up the good work.



Ok cool, just wanted to make sure I wasn't making things harder or teaching people bad habits!


----------



## Jon von der Heyden (Feb 12, 2014)

Superstar31 said:


> Ok cool, just wanted to make sure I wasn't making things harder or teaching people bad habits!


Well if we weren't keeping an eye on you, we are now


----------



## Jonmo1 (Feb 13, 2014)

Santa pays more attention to children who ask if he's watching...


----------



## Superstar31 (Feb 13, 2014)

Jon von der Heyden said:


> Well if we weren't keeping an eye on you, we are now





Jonmo1 said:


> Santa pays more attention to children who ask if he's watching...



Sometimes attention isn't a bad thing! plus i'm actually interested in seeing simpler ways of doing things... i've always been known to overcomplicate things

side note.... How do I get Excel Jeanie in english?


----------



## Smitty (Feb 13, 2014)

Jon von der Heyden said:


> Well if we weren't keeping an eye on you, we are now



Double Secret Probation for sure.


----------



## HalfAce (Feb 14, 2014)

Yep, you're under the microscope now!!!


----------



## dispelthemyth (Feb 18, 2014)

Superstar31 said:


> (minus macros...* I hate macros)*




blasphemy


Also if the OP doesn't come back saying it does not work, then you can assume you have done what's needed

Just remember because you do something 1 way and someone else posts a completely different answer, you are not wrong. With Excel there can be dozen if not hundreds + ways of doing the same task.


----------



## Superstar31 (Feb 18, 2014)

dispelthemyth said:


> blasphemy
> 
> 
> Also if the OP doesn't come back saying it does not work, then you can assume you have done what's needed
> ...



LOL... I do need to learn how to make them.. and I don't make recorded macro eithers 

Very true about different ways of doing things.. I just want to learn simpler ways of doing it sometimes. 

and I like your comment about the op not coming back and saying it doesn't work, because I never thought about it that way. I always figured I somehow crashed their excel, which in turn crashed their computer, which in turn took out their internet, which in turn made them leave the house, which in turn made the do something daring in life.. isn't that how that commerical goes


----------



## Jon von der Heyden (Feb 18, 2014)

I flat outright refused to learn macro's for ages.  And I'm glad because by the time I started I knew Excel inside out.  There's a hell of a lot you can do without VBA - so unless you really need it in your work, don't rush.


----------



## diddi (Feb 19, 2014)

VBA is such a great way to reduce thousands of cells of repeated formulae to a few lines of code. ive reduced multi-mb files to a few hundred kb.


----------



## Smitty (Feb 19, 2014)

Diddi/Jon,

I agree with both of you.  VBA can be an essential timesaver, but far too often people try to do in VBA what they can do natively.  A prime example is Pivot Tables.

I didn't even start to learn code until I'd used Excel for over 5 years, and I'm still learning.


----------



## Jon von der Heyden (Feb 20, 2014)

Smitty said:


> Diddi/Jon,
> 
> I agree with both of you.  VBA can be an essential timesaver, but far too often people try to do in VBA what they can do natively.  A prime example is Pivot Tables.
> 
> I didn't even start to learn code until I'd used Excel for over 5 years, and I'm still learning.



Yup mine was a 5 year wait at least too.  Of course I agree that VBA is hugely powerful and I am so pleased that I invested the time into VBA that I did.  And I'm also very grateful to the many members here that have invested their time in my learning.  I've learned alot from Smitty and many others, but not just the MVP's here.  There are hundereds of others that I have learned from.  

But in my experience almost all capable VBA developers went through a phase that I call "a dangerous VBA developer phase".  When I teach Excel/VBA I also call it the "****y phase".  I don't have PEDII to hand but I like the way they summarised the phases.  One of them was a phase where the developer sees every problem as needing a VBA solution.  I absolutely hate seeing projects with copious modules and code where native functionality would have been a much better option.

The problem is so often people who learn VBA invest just about enough time studying it that they never mature beyond that phase.  That's one of the side-effects of VBA being mostly a "self-taught" programming language.  Most never embark on any form of study of proper programming practices.  Learning methods and functions is not enough...  Or on the other side of the spectrum I sometimes see people from a programming background start developing in VBA yet many don't invest the time to properly study the Excel Object Model and discover the native functionality that Excel offers.  In these cases again you see buckets of code (usually structured somewhat better), but where VBA is used to solve problems that a pivot table etc. could easily solve.

Sorry if I sound like the sceptic here, but in my experience 90% plus of people that I meet in companies (that I have worked for or consulted to) should not be allowed to employ VBA in their projects.  That is, not without proper training.  These days I steer well clear of projects where I need to adapt or inherit someone elses project; it's often just so much quicker to learn the requirements and start from scratch...  There are plenty of people on this forum that I would happily work with and be happy to inherit their projects, but unfortunately (again in my experience) most people I meet through my work have never heard of MrExcel and never contribute to any of the Excel/VBA forums.  I honestly don't think books are enough and most VBA training programs I see out there are hopelessly inadequate.  Even forum lurking isn't enough - you gotta get stuck in and tackle problms that you find challenging.  Learning VBA (and the programming discipline) takes months/years, not 2-3 days...

Like Smitty, after many many years, I'm still learning.  These days I find less time to devote to the discipline (due to work loads and other hobbies), but I try and keep active enough to keep on learning.


----------



## BrianMH (Feb 20, 2014)

Jon von der Heyden said:


> These days I steer well clear of projects where I need to adapt or inherit someone elses project; it's often just so much quicker to learn the requirements and start from scratch...



Agree with this so much.  Personally I actually started out with VBA because I was working on something that was taking 2 people an entire day to do and it was very manual.  I found out about automation and got stuck in. My early code was poor of course but I loved it and learned much.  I was one of those that always wanted to solve something with VBA.  I was pretty active on VBAX but when it stopped working properly on chrome I wandered over here and started to see more questions about formulas than VBA.  I got stuck in and learned there was so much I could do with out VBA.  Now when I come at a problem I want to solve it with a formula if I can (and if it would be more efficient).  I still use VBA daily and I love the fact it is there but I'm glad I now understand the concepts of complex formulas.  I feel I am much more of a well rounded user.


----------



## Smitty (Feb 20, 2014)

Well put guys.

And I've been on the receiving end of a few projects with some horrible code in it (e.g. 347 declarations like "Dim ExternalData_1") instead of a simple loop, and no comments.  In one case I got over 1,000 lines of code down to 4-5, just by stopping and taking a look.   On the other side, I'm dealing with a workbook right now that literally has 680 static address named ranges (for one series of data), but the data range changes and the named ranges don't!

It is unfortunate that there aren't any really good VBA courses, but like Jon said, it is very much a self-taught language.  It's yet another example of Microsoft doing a ****ty job of promoting something so widely used in business.


----------



## Superstar31 (Feb 20, 2014)

Since this thread has gotten so much traction, can one of you answer this for me  http://www.mrexcel.com/forum/excel-questions/757886-vlookup-embedded-if-statements.html

I'm not sure if it's even possible.....

On a note related to this thread.... I'm all about index match match, to the point i try to use it for everything and just like many people use vba...


----------



## dispelthemyth (Feb 20, 2014)

Superstar31 said:


> Since this thread has gotten so much traction, can one of you answer this for me  http://www.mrexcel.com/forum/excel-questions/757886-vlookup-embedded-if-statements.html
> 
> I'm not sure if it's even possible.....
> 
> On a note related to this thread.... I'm all about index match match, to the point i try to use it for everything and just like many people use vba...




Index/Match is good but it has its limitations, I generally start my through process 

1) Can i think of a formula that may work
2) if the formula practical to use or will it bog down the processor
2a) write formula using trial and error, thinking of what formuals may work best
3) Start VBA is needed



FYI I have attempted the question you posted


----------



## diddi (Feb 21, 2014)

I started programming in 1977 (FORTRAN) and used excel sans vba for quite a while before my "great revelation moment" when I discovered that excel actually had a vb layer back in about 2001. LOL


----------



## Jon von der Heyden (Feb 21, 2014)

Superstar31 said:


> I'm all about index match match, to the point i try to use it for everything and just like many people use vba...



VLOOKUP and INDEX/MATCH constructs are of the most _over used_ functions.  They are amazing when used properly but I frequently get requests from people to review their spreadsheets and work out why they are so darn slow.  A typical culprit is excessive use of linear search lookups rather than binary search lookups, and usually around 80% of the time with small tweaks they can be substituted for binary search lookups.

In my course I illustrate the efficiency of binary over linear.  I have a list of 15000 social security numbers (fake ones) and we do a VLOOKUP against a table of 100000 ssn's with associated names, DOB etc.  I set Excel to manual calc mode and when I hit recalc I get the class to count how many seconds until calc is completed, usually around 35-42 seconds.

Then I sort the table and use a binary search VLOOKUP (a double-jointed one to emulate #N/A behaviour of linear search VLOOKUP).  Again the class counts the recalc time, only this time it's done in less than 1 second.

More on binary/linear search: Extract a number from a text string with LOOKUP Jon von der Heyden


----------



## dispelthemyth (Feb 21, 2014)

Very good article Jon, very clear explanation of what occurs step by step


----------



## Domski (Feb 21, 2014)

Jon von der Heyden said:


> I have a list of 15000 social security numbers (fake ones) and we do a VLOOKUP against a table of 100000 ssn's with associated names, DOB etc. I set Excel to manual calc mode and when I hit recalc I get the class to count how many seconds until calc is completed, usually around 35-42 seconds.



I ring IT and ask for a faster PC before repeating this and finding it takes no time at all .

Dom


----------



## AlphaFrog (Mar 7, 2014)

I just got notified today of the Feb 21st replies to this thread. What gives

Nice article on _Binary Lookup_, Jon. There are several other nuggets on your site as well. The _PrankBackfire _sub is sick. I like it. BTW; what's your phone number


----------



## RoryA (Mar 7, 2014)

I think the notif you got was from a Spam post that got tanked.


----------



## AlphaFrog (Mar 7, 2014)

RoryA said:


> I think the notif you got was from a Spam post that got tanked.


Ahh. Thanks for the feedback.


----------



## dispelthemyth (Mar 7, 2014)

It also made no sense so its was not good quality spam


----------



## Jon von der Heyden (Mar 8, 2014)

AlphaFrog said:


> Nice article on _Binary Lookup_, Jon. There are several other nuggets on your site as well. The _PrankBackfire _sub is sick. I like it.


Thanks.   I may one day actually get the time to complete the dozens of partially written articles on the blog.  I always start these things and then never have the time to complete.



AlphaFrog said:


> BTW; what's your phone number


Haha!  Stick we the number in the routine!  Although I think the kid changed it after 2 weeks of continuous calls.


----------

