When I make a post here, I always wonder how many people actually benefit. Is it too advanced? Too simplistic? In other words, I have no idea what kind of Excel users actually read this blog.
So how about a poll? Please reply, even if you don’t actively participate here by posting comments.
I am a graduate student in mechanical engineering at Oklahoma State University. I feel I am in the minority of M.E.’s due to my fondness to programming. I frequent your website here and appreciate all the tips and help you provide, it definitely goes to good use.
Compared to you guys I guess I’m a beginner in VBA, also in C#. A lot of the code I’ve been seeing lately I would never really use, too advanced. Your example on the array vs. looping is very helpful. Are there design patterns in VBA? That would be good.
Poll is a nice ideaof course, everyone has a different (and probably conflicting) idea of what ‘advanced’ means… Maybe you could provide some guidelines. Or maybe poll what features users use (can I say that?) I myself don’t know what a pivot table isam I just fooling myself by considering myself advanced?
Design patterns? I don’t know what that means.
Very difficult question to answer given the expertise of many of the contributors to this blog.
In my personal experience, I’ve found that the bulk of Excel users (including many who say they know it very well) know probably 5 to 10 percent of what you can actually do with it. I would guestimate that this group probably comprises maybe 80 to 90 percent of all Excel users.
Of that last 10 to 20 percent, probably 99.9% of them fall into the Intermediate to Advanced users. I’d like to think of myself in that category (probably at the lower threshold).
That last .1% are people like yourself, Dick, Chip, Ogilvy, Tushar, Rob, David (and even Harlan) to just name a few.
Some of the stuff that I see here, I probably wouldn’t ever use but it’s very informative to know what can be done.
I’ll never know when I might use some of the more advanced stuff here but if I ever do, I could use the posts here as a starting point and finish up in the newsgroups if I needed more help.
I’d like to see more of the Intermediate topics here but not at the expense of the Advanced (and ultra advanced) topics.
Just my humble opinion,
John
There are books out for C++, C#, Java that shows code examples on “design patterns”, code that can be re-used in certain situations. Right now I’m tinkering with Excel and databases. Code examples with ADO would be good too. Thank you guys in advance, this site is a big help.
I think it is important for the “posters” to keep posting a spread of info aimed at different levels.
Although I consider myself in the very advanced category, it is amazing that every now and then a little Jem pops up from left field often hidden away in the example code or mentioned in passing in a post.
I think it is more important to see a spread of the many many uses of Excel, as that is where people start to think and relate solutions or ideas to their own environments.
How about a Poll on Ages and Locations of readers ?
Hui…
Re: design patterns…
I posted something to the Excel vba NG a couple of years ago asking for some direction. One answer I got was, I believe, from Tom Ogilvy. Something to the effect of if your code does what it’s supposed to do and doesn’t to what it’s not supposed to do, then it must be good code.
So looking for some additional direction, I asked more questions. Being a CPA, I asked if there were any “generally accepted” coding methodologies for vba. Dick was kind enough to respond noting that he has his “generally accepted” methods for doing certain things in vba, most of which were picked up from other people whom (or who? I never get that right) had their own ideas of “generally accepted” coding practices.
So I guess after all of that typing, I provided pretty much could be a non-answer, but I’m not going to delete it now. I think you just pick things up by reading the NGs and the stuff here and other Excel vba websites, all of which a usually a big help (and free, thanks to the generosity of the knowledgable Excel VBA folks — Thanks, Dick and everyone else!).
Hi John,
I don’t know what type of excel user I am, neither I am in a position to assess the same. If I see my surroundings, then I might consider myself a very advanced user, but seeing many of the blogs (particularly this one) and help forms, I feel very humbled.
In my opinion, as long as you know that you can do something better and try to achieve that, you will improve. That’s why people keep visiting blogs like this one. AND YOU PEOPLE ARE HERE WE KNOW, THE TORCH BEARERS…………. to inspire every bit in us.
Regards
Kanwaljit
I think a very good book on this topis is “code complete” (there is a 2nd edition) by McConnell.
Get hold of a copy!
Why not include a feature on your posts (along with a corresponding link in your rss feeds, for those that dont visit the site), that allows users to vote on the helpfulness of the tip?
Was this tip helpful?
or
Was this tip too advanced?
Something along those lines..
Then you can trend the data and serve tips accordingly.
Not wishing to be picky but surely our own view of whether we are Advanced or Very Advanced depends on what we measure ourselves against. You could take the view that you are only an advanced user if you make your living out of writing books about it. Also, I usually advise people against having an odd number of categories in a survey because of the people who always go for the middle option regardless of the wording placed around it.
I went with “Advanced”, but I agree with some of the other posters – it depends who you’re comparing yourself to.
Plus I’d never claim to be top rung – there’s not much room on it! Now “Fairly Advanced” would be good. That could come before “Quite Advanced” and just after “Pretty Darn Avanced except on a Friday afternoon”.
“it depends who you’re comparing yourself to.”
It reminds me of the new graduate from medical school coming home and saying “Mom, Pop, I’m a doctor!”
His mom looks at him lovingly and says “Son, to me you’re a doctor, to your father you’re a doctor, but to a doctor, you’re not a doctor.”
OK: I know enough about VBA to get by in, I know more than I originally intended to about weird erroneous formula constructs, enough UI operations to understand Bob Umlas’ Magic book but not sufficiently motivated to actually use most of the array stuff.
Ditto to “Some of the stuff that I see here, I probably wouldn’t ever use but it’s very informative to know what can be done.”
Try: Pretty advanced but forget a lot of things I don’t use often.
P.
Probably should start a new thread on this.
JW said: “Design patterns? I don’t know what that means.”
I didn’t see any ironic smiley there, so I’m going to take a chance that he meant that.
I have the “Design Patterns” book but frankly never used it.
Many of the books on spreadsheets for specific applications (eg engineering, accounting, etc) can be regarded as collctions of design patterns – “this is how you do a …”. But these are more like templates. There is rarely any discussion on WHY ‘x’ is a good solution, and preferred to others.
Design Patterns are less than templates, and more than the 1000 tips and tricks that JW and BU and many others provide us with to shorten our work day.
DP are reusable fragments or structures of data and formulas that express a frequently occurring situation sufficiently generically that they can be adopted to even more specific cases by the developer.
Contrast with Anti-Patterns that are generally regarded as a Bad Way of doing something because they make other desirable things impossible : expansion, comprehension, usability, …
Hi
All those who have polled “Very advanced “
Please standup,Please standup :)
Xcelion
I have “Design Patterns” and refer to it regularly when I’m working in C# and (less frequently) other compiled/OO languages. I can’t say that I have ever perceived much of a need for them in VBA*. I suppose that there could be a set of Excel/VBA specific patterns, that might make sense. But off-hand I can’t think what they might be.
*Actually, I have just thought of one! I use the Factory pattern – somewhat modified – to get around the instantiation restrictions in VBA: when I want to use an object from another workbook I need that workbook to provide a Factory to create the object because VBA restricts my class Instancing to “PublicNotCreatable”. And of course there’s Facade, but I found that one myself years before – I called it a wrapper.
There is an Excel ‘ability’ scale on Aaron Bloods website (http://www.xl-logic.com/user_scale.html). On his scale I’m an Advanced user (with a toe in Expert). A lot depends on what challenges you are presented with: if you make your living from producing Excel-based solutions for example.
I’m a regular reader of this blog, I like to see how people tackle problems in different ways and the occasional “I never knew it could do that!!” moments. Often the topics are beyond me and I think carefully before posting as the problems under discussion are usually more subtle than they first appear.
well actually design patterns are code and also more, there were good articles lately on yahoo ui blog (the javascript library) design patterns can be anything as long as it goes also in the way the user expects it to go, like help icons to the right of a field to name one, so it is the code behind it and the design (hence the design name).
but the design patern ideology is often used to refer only to the code.
also for excel you can only evaluate yourself against the part of excel you do use.
I consider myself advanced as I know how to use most formulas in excel includint array formulas and DB stuff. I know a lot of vba including handling and creating events (with classes) etc… even use some API stuff if needed,
though If I go into maths and stats formulas then I am a beginner.
Excel is oriented to many kind of users, so I guess it is difficult to be an expert in every single area of excel as multiple true experts are working behind the program to make it the way it is.
I even saw in some companies some IT guys very proud to do a lot of VBA in excel, just to discover (I showed them) that all they did could be done without a single line of code and give better results. though their VBA code was very well structured and documented…
so I guess you have to define what you mean by advanced and compared to who
I’m a big Excel fan – I make spreadsheets for *everything*. (To the amusement of my wife.) Every now and then I need some code to do something out of the ordinary. I usually bludgeon my way through it… I start off with recording a macro, then I edit the Visual Basic to enhance the code. I always end up searching the web for some other code snippets or digging back through other code that I’ve appropriated.
So – I can do more than most users, but far less than the the “unmost”.
I’m a regular reader and think the level at which the posts are pitched varies considerably depending on the topic – and that’s a really good thing. I like trying to follow the more complex code-heavy posts to increase my own knowledge but also enjoy taking a fresh look at everyday functions.
For example, the recent post about the Goal Seek function was perfect. My initial reaction like others was “Use algebra!” but the discussion moved on to see the problem from other points of view. This is a lesson I try to take on board when I try to show colleagues Excel’s potential even at a very basic level.
I don’t know what ability level I’m at (or care) but I know it’s increasing all the time thanks to the contributors here. So Thanks. And more of the same please.
Can you:
Use API’s , Write Class Modules, Use Array Formulas {correcly}, Write your own dynamic toolbar addin, parse any data in any format.
Basically: Do eveything that the posters to this blog can do, save one (Goal Seek, hehe).
Then you are An Expert!
I am a between a beginner to intermediate. This blog would be more meaningful to me if you increase the mix of information to reflect this.
I just wanted to add a note of clarification. I use Excel every day in my job, frequently write macros in VBA to automate data manipulation or output from Excel to other formats. I’ve also done some integration of Excel with other office apps (Word and Access). I marked advanced. Thanx.
At work I’m considered the Excel guru – replacing tasks that take hours with macros that run in seconds, replacing the need to manually enter data with formulae, controlling Outlook from Excel, etc.
The user scale on Aaron Blood’s site would put me between Advanced and Expert and I regularly answer questions on MrExcel.com, but compared to most of the contributors to this blog, I’m a complete newbie.
Can you write an add-in which can be installed without problems by a non-expert, which has its own UI (menu or command bar presence, appropriate user forms), can handle data input and output, data import and export, remembers settings from one use to the next, and it “just runs” without spewing error messages and halting execution?
Then you’re an expert (“Very Advanced” in John’s scale). I’m almost there.
I am an advanced or expert “user” of excel. However, it terms of using VBA, I am a novice or beginner. I have written some VBA, but do not consider myself knowledgeable. I work in accounting and do not do a lot of repetitive tasks that can be converted to VB. I do enjoy reading about macros that people have written and actually aspire to do some of the work that they do!
I am under the impression that these kinds of forums are for those who USE the product and have an interest in becoming better. (When I was young and dumb I competed in rodeo riding bulls and broncs. The way to get better was to draw the best stock and compete against the best contestants; and I believe this is true of any serious endeavor.) I have always been appreciative of the advanced level of talent and expertise presented on this site; which presents a broadening of my Excel experience that I would not normally have encountered.
Thank you
doco
Thanks for this site! I reference it quite a bit. I checked “Advanced” but would say that it depends on what context. For VBA and Form-like stuff, I’d say Beginner, but for most things financial related, its def. Advanced. Its funny how if you know just a few advanced things, you can seem to solve 90% of the issues people face when they are working with large amounts of data. I’m a CPA and freelance consultant and have found this to be the case when I go on client engagements. Thanks again!!!
I would call myself Intermediate to Advanced on Excel, but Beginner on VBA programming. To my mind, there’s a difference.
I’m only halfway through JW’s Power Programming but have a good grasp of formulas and techniques. I’m advanced in the office and would’t even show up on the radar here.
Brett
I put down Very Advanced. That being said I am humbled by this product on just about a daily basis. I tend to read most of the posts. I would have to say that most of my favorite articles revolve around simple solutions to otherwise complex problems. I tend to judge spreadsheets not by how complex they are but rather by how simple they are to use. The spreadsheet that solves the trickiest problem in the simplest way wins every time (IMO). The problem is that making something appear simple is often very complex. This forum is great for showing varying degrees of complex ways to make spreadsheets appear very simple. The solutions apply to beginners and experts alike. I point to the number of threads that begin with “Here is what should be a simple real world problem and here is how I fixed it! Any comments???”
By the time I got home from work today Aaron had completely re-modelled his website!
Excel User Scale now at…
http://www.xl-logic.com/modules.php?name=Content&pa=showpage&pid=1
I like Dave’s definition above, because I can do those things, but realistically I have to go with Jon’s and call myself advanced.
I really appreciate how available the experts are, here and on the newsgroups, and how helpful and patient they are. For some reason this seems particularly true of the Excel online community.
Agree with others that we may not be the best judges of our OWN skill-levels.!
But if we didn’t all really apppreciate the wide range of subjects that you – and others – contribute, we would not keep coming back! I’ve got you on my browser home-page and would not miss ANY opportunity to learn something new/different/quicker/smarter. I reckon that I exhibit ‘withdrawal symptoms’ on days with no new posts!
Excel is so incredibly underused (for it’s cpabilities) and whilst some posts may not have a specific/direct relevance (at the time), if it sparks an interesting new train of thought/exploration, then your own contribution(s) will not have been wasted.
Please keep the blog going…AS IS. Don’t get too set on ‘navel-gazing’.
We who browse you, much appreciate you!!
Barry (Bath, UK)
Looking at Aaron’s scale, I’ll tag along with gruff in the Advanced-borderline-Expert pool.
I come here regularly to see the orginal, often mind-expanding solutions to problems that I might necessarily face personally, but often take something useful away from the post that I can eventually apply to my own challenges.
Hi,
I’ve been reading this blog pretty much since the start, and many of the early articles were pitched at just the right level for me – I guess I’m an intermediate user. For example, I was interested to learn about the various ways of passing parameters to and from userforms, debates about whether to use integers or longs etc. Over the past year the articles seem to have become much more arcane (Quickbooks, xml, ’07ribbon) and the blog has left me behind, I guess. I was struck by the level and tone of John’s recent post on arrays because it reminded me of where Dick’s blog was a few years ago. I didn’t mind that happened to know this one – it was a pleasure to read anyway, and always nice to see others’ code to common problems. So while I appreciate the need for the cutting edge, for my money I’d also like to see ever-more examples of common intermediate stuff (userforms, using ADO to grab data from other open sheets or Access, controlling cascading events and so on). I know this is all well-trodden territory for most of you, and that I can get the answers for these things by digging around on google, but one point of the blog, I thought, was to chew on some things that we all do frequently and to compare notes…
Cheers!
but one point of the blog, I thought, was to chew on some things that we all do frequently and to compare notes
Good point, Graham. I think those are my favorite types of posts. Even if I think I’ve mastered something, someone always comes up with a better way. I’ll try to do more of those types of posts.
Congratulations, this is a great way to get feedback.
I’d like to see more on charting, one of John Walkenbach’s areas of expertise.
Other topics: building add-ins, reusable code, structured coding, best VBA practices.
When Excel 12 hits, perhaps a second page dedicated to Excel 12 would be good.
In my estimation, most users who frequent this board are relatively well versed in Excel or have the motivation to learn. It would be nice if others could submit material to one of the moderators to post.
IMVHO……
The only people that can assess our skill levels are that .1% that really live and breathe this stuff.
I can write some code one day and forget what it does the very next day.
It absolutely amazes me that John can still write books on this stuff when he (apparently) does not actively use it (as a consultant/programmer/whatever) and doesn’t participate in the newsgroups (where one can hone his/her expertise). How (in Gods name (yeah, I’m an atheist too )) do you remember this stuff from day to day. My congratulations to you John. You “do” live and breathe Excel. If it were possible to remember everything that I’ve learned from you and the other guru’s, I could maybe be a peer.
Back to the topic and something I’ve thought about doing for a while (and this post is giving me the opportunity do do it) is to post one of my works and have the guru’s (and others) assess it (and my expertise) for me.
The following I did on a whim. My son had joined the NYC Fire Department and they gave him a calendar of his schedule for the year. Noticed that he was working that Christmas and asked him if he was working the following Christmas and he couldn’t tell me.
Repeating schedule, groups, numbers assigned to groups, etc……..
I can do this in Excel!!!!!!!
This is still a work in progress so don’t rate me on loose ends.
Actually (and I’m sure that everyone will agree) that a workbook is never finished.
There’s always a way to make it better.
If you care to take the time to rate me, do it on concept, design and ingenuity.
For some reason (and I’m sure that the guru’s can figure out why in a heartbeat) this doesn’t work when opened within the web but will work perfectly when you download and open it.
My VBA (I’ll admit) needs help, But (I think) the concept and design deserve merit.
I also believe (and I’m already working on it) that something like this can be modified for a lot of different applications.
Comments are welcome (negative or otherwise) and I hope that someone may find something that they can use for their own purposes in this.
http://nbpl.home.att.net/fdnysched4web.xls
I have followed the site from earliest days, and have enjoyed the posts and variety of topics and levels of difficulty/expertise. But as others have noted, recently the focus has shifted away from areas that cause me to think differently and challenge me to learn more. The recent posts are either far beyond me or do not have direct or indirect influence on my work.
What level am I? Hmmm, probably at least three different levels, depending on the context.
Work scale: I walk on water as the XL guru!
Aaron’s scale: Advanced
Daily Dose scale: Intermediate
Realistically around here I probably couldn’t re-fill the coffee cups for the main posters and those who interact with them. ;)
But it is great to be in the company of such stars (on this blog and several forums), whether you are Guru, Expert, Advanced, Intermediate, or Beginner. Yes, at one time all of us were beginners (except perhaps Bob Umlas, or John W, …), I didn’t even begin to use Excel until after I turned 50. And prior to getting John’s Power Programming book in 2002, my last exposure to programming had been in college using Fortran IV with an IBM 360 (when it was cutting edge!).
Anyway, keep up the good work, and remember some of us who aren’t Gurus or even Advanced users. Set the standard far enough ahead to inspire us, but occasionally come back and walk with us and encourage us. We like to learn!!
Ok authors, I guess the best thing to do is mix it up level wise. Some posts advanced some intermediate. I think I’d prefer mostly coding in VBA posts rather than Excel stuff. Thanks again.
I am self learner, somewhere between intermediate and advanced and really benefit from this site especially on VBA codes.
just to follow up, the link to yahoo design patterns:
http://developer.yahoo.com/ypatterns/
I feel like I fall into the grey area between intermediate and advanced. I spend a lot of time analyzing and manipulating data, querying databases using SQL, integrating different software with Excel using VBA.
Dick, I can’t tell you how much I’ve learned from this sight and of course John’s books. There is a lot of content posted on this sight which keeps my ego in check (especially when xldennis and Tushar post). Excel has so many uses that cross over so many aspects of business and life I think it is difficult to have pretty specific requirements to categorize users. I for one very rarely use pivot tables, I know how to create them I just choose not to use them because I don’t like how they look in print and I find in my industry that most people don’t know what pivot tables are or how to use them. I also don’t use a lot of the functionality (statistical, financial etc…) because I don’t have a specific need, and my charts pale in comparison to John Peltier so does that mean I don’t qualify for a certain group? If I don’t use something that Excel has to offer I atleast try to be aware of the option and how and when to use it because you never know when you’ll need it.
It’s impossible for any one person to know everything about Excel. Everyone utilizes Excel differently and that is what is great about this sight because I can see how other people from different walks of life use the software and that might trigger an idea for me to create something that I’ve never done before! So keep the posts coming no matter how basic or advanced.
A number of posts in this thread express a desire to learn more about Excel.
For those that arent’t familiar with it, participating in the Excel newsgroups is (IMHO) one of the best ways to learn.
A couple of years ago, I had the time to be an active participant. Trying to answer some of the questions posted there give you a very diversified view of how others are trying to use it. Trying to come up with an answer to help someone out is quite rewarding and also gives you the opportunity to hone your own skills and learn a lot of new ways to use Excel.
I learned more by trying to help others than I could ever learn on my own by reading a book or just experimenting on my own.
Sometimes, you’ll answer a question and one of the guru’s there will show you a better way to do it or correct you if you make an error.
Whether or not you participate in answering questions, if you ask one yourself, you’ll usually get a prompt and detailed answer (sometimes within just a few minutes).
When I had the time to participate, I found it quite challenging to answer a question before anyone else did.
You’ll find that many of the gurus that participate here in Dick’s site are also active participants in the newsgroups.
For a little more info on the newsgroups (and how to access them) visit j-walks link here:
http://j-walk.com/ss/help/newsgrp.htm
That newsgroup page was written about 10 years ago, and last updated about 4 years ago. Man, that Spreadsheet Page needs some serious work!
That newsgroup page still works John and the “Spreadsheet Page” has enough great information and examples to keep a novice/intermediate (and even advanced) user busy for a very long time. And there are new users “born” every day.
Got my start in Lotus and vowed never to migrate to Excel. Bad choice. Wasted a lot of time.
Took 3 courses in Excel (basic/intermediate/advanced). Learned very little.
Happened upon your “Spreadsheet Page”. Learned a lot.
Bought and read a few of your books. Learned a lot more.
Found the newsgroups and got hooked on it for a couple of years.
Thought it was really cool when you actually answered a couple of my questions there. Wow!!! Here’s the guy that “wrote the book” and he’s giving me personal help.
All the reading (books, websites, etc.) can’t hold a candle though to what you can learn by trying to help others with their problems in the newsgroups especially when you know that if you make a mistake, Ogilvy (or one of the other gurus) is always looking over your shoulder. I honestly believe that Tom reads every single post and reply in all the Excel newsgroups.
Oh, and by the way, John……..
Thanks
I said advanced, by which I mean I know nearly every formula and a lot of advanced tricks, can link to Access and such, I can read the Excel Bible and only learn a few new things, I can write original VBA to automate most tasks (though my object model understanding still is crappy so I end up with longer code than needed) and I can write VBA to connect to other Office apps to get stuff done.
What I like seeing here is VBA code examples, mostly. And I like the occasional “regular Excel” challenges that Dick tosses out. Not the softballs, but the hard ones that make you think about a different way to solve a problem.
That said, when I train people at my company in Excel, if they show any interest in what a great program it is, I direct them her and to various other web resources, including DDoE, for further learning opportunities. So for them, some moderately unusual stuff would be great, like the goal seek example a few days ago.
I also will freely lend them the Polish version of Excel 2003 Power Programming in VBA that I won from a contest on this very site. And I make sure that they note the author’s signature. After which I am usually declared to be even geekier than anyone had even told them.
Thank you for the kind words, John.
I think I’m going through some type of late-life crisis with Excel. At this point, I’ve pretty much decided that this recent round of Excel book revisions is the last one for me. Well, maybe I’ll hang on to “Excel Bible” just to pay the mortgage.
But I really have no idea where MS is going with their Office programmability. I know zip about SharePoint, Excel 2007 server, and .NET stuff. Literally nothing at all, and I have no interest in learning about it. I think the technology is passing me by, and if readers are expecting to learn about this stuff, I can’t deliver. My Excel guru status is on shaky grounds these days.
I’d consider myself an intermediate user, and I’d like to echo the thoughts about how useful this blog has been to me. I work as an accountant, and I’ve managed to become moderately adept at cobbling code snippets gleaned from this site and elsewhere around the web into useful vba macros and add-ins. John’s books have been incredibly helpful to me. I own and use Excel Formulas, Power Programming, and Excel Charts extensively, and I can’t thank John enough for all of the information I’ve gleaned.
Regarding John’s comments about his shaky Excel Guru status: I’m concerned that Excel & vba will go the way of Visual Basic and move out of the realm of the casual user and exclusively into the hands of professional programmers. One of great things about Excel and VBA is that I can write really useful macros without involving the big guns in my company’s IT department. I think that there is a great need for small, tightly focused macros and routines that are just a little beyond the average office worker’s ability and just below the threshold of a professional programmer.
My heartfelt thanks to all of the great contributors to this blog, you perform a great service.
David
John, my guess is that using the actual model of vba is either going to end with a fork too important to make it backward compatible with old versions, so that would be a reason to move onto a new language.
the problem I can see with this is the following: today in the office, the regular excel power user does not have access to any visual studio possibilities as he usually isnt a member of the IT group.
I am waiting to see exactly what kind of tools will come packed with office 2007, will vsto be usable without visual studio (as a standalone tool) by using some kind of personnal visual studio version packed with office, then it might take off and interest more than a couple of persons.
As for interest for new excel 2007 server and sharepoint, I dont know if that will be usable for the end user or not, I know I would just love to design dashboards auto updating and available to the user through a nice portal, it seems to be geared toward that kind of stuff, but once again if the end user as no power over the programming needed for this, IMO it is useless (IT groups are most liekely to use more web powered tools like crystal reports, or opt for ready to go solutions like microstrategy etc…)
I chose advanced here, but really I suppose I am between Intermediate and Advanced (Advanced & Expert on Aaron’s User Scale).
A really useful (and a little humbling) set of tests can be found here:
http://www.ms-iq.com
Try doing the Excel one without having Excel open in front of you… Good fun for a Friday afternoon!
Well, Alderaic: MSFT has announced VSTA recently, so…..
ha then THIS is good news ;) as long as everything comes packed with office, we will find ideas tutorials and websites to make things simple.
I mean VBA was a big thing to start working with too and took a lot of error and trials for almos all users (I guess but still..)
On the other side, the compatibility break with the new files will probably get companies to totally switch to the new office version and remove some of the headaches to maintain backward compatible code too.
I will go and look for that VSTA thing
This is my thought exactly! While reading through posts about Excel 12 here and elsewhere I was impressed that the tool is fast getting away from those who were formerly known as ‘power users’ and migrating to that airy and elite group known as ‘professionals’ and, no doubt, for a pri$e.
Several years ago now I undertook the notion to learn JAVA. After a year or so I gave it up. Seemingly everyday a new accronym representation of it’s derivitives appeared (see javaranch.com for a small listing) . Who can keep up?
Sadly, I see the similarities now with MSFT…BASIC is no longer!
I’ve only just come across your site but have already learned so much that will help me to write better code. A superb site – manby, many thanks!
I think VBA will always be available with Excel. If not competitors will have an advantage, if they have their own scripting language. VSTO is over-kill for small but very helpful snippets of code.
However it wouldn’t hurt to learn C# and the .Net Framework, it really isn’t that hard. I can’t believe I’m coding in C# with GUI and all. No way am I as good as enterprise level programmers, but I have no programming background. VBA is the first language I learned and that was only a little over a year ago. I’ve never even heard of .Net before until Stephen Bullen posted his petition to preserve VB6/VBA…that’s when I started to read up on C# & .Net.
Isn’t this a bit like asking “do you have a good sense of humour?” “or how attractive are you to the opposite sex?” etc….
Simon H,
Ouch!! That was sobering. I don’t have 2003 at the office and I would like to see an answer key at the end. Guess I need more books!
Brett
I just took the tests linked by Simon. It says I’m an intermediate Excel user but an advanced programmer. I’d agree to the extent that I’ve gone through the phase of relying too much on VBA when worksheet solutions would suffice, i.e., using conditional formatting, data validation, dynamic ranges, hidden helper columns, etc. What I really hunger for is more help designing worksheet interfaces. PED has a nice introductory chapter, but I haven’t seen much else in the way of resources.
I took the test and got Advanced, 13/15, on both Excel and VBA. But these tests always bug me. The whole interface of Office and the VBE are designed so you don’t have to memorize lots of crap, because you can see what you need to do. Also the question about the 3D chart doesn’t have the correct answer, which is “Don’t waste time putting an image in any of these surfaces, and convert it to a 2D chart instead.”
Nobody even looked at the workbook that I posted :-(
Gosh, dang, darnit.
Okay, scratch one of those “intermediates” and add one for a “beginner”
John,
I just took a look at your work. I think you can hang on to your intermediate status with confidence :). The calendar looks clean and displays a lot of info. A lot! How long did it take you to code?
Hi John (Wilson),
You asked for it…
I opened the calender form. The spinbutton that are supposed to increase/decrease months and years didn’t quite work as expected. The top spinbutton is only enables me to click up, down has no effect I can discern. The lower spinbutton increases the year when I click the down arrow, this is counter intuitive.
I got a runtime error when choosing a month from the dropdown. Probably because I have Dutch date settings.
I’d advise to use tooltips on your form, so the function of the controls is clear.
You’ve declared a host of variables as public, even when they are only used in a single module
I strongly prefer all variable decalrations in one place: at the top of each procedure.
You haven’t named your controls on your forms, but used the default names. I prefer names that describe their function, preceded with a three letter indication of their type.
I leave the rest to others to comment on…
Jan Karel…
Yes, I did “ask for it”
Thanks for taking the time to look at it.
I suspect that you opened it within IE and it will give run time errors trying to select a worksheet? Not sure why, but “selecting” is a nono. Shouldn’t happen but better coding on my part should be able to get around it. If downloaded and opened, it’s pretty much error free. Works okay when opened in FireFox (you’d think MS would do better with compatibility with their own products?)
The “ToolTips” is a great idea. Thanks. I’ll work that into my next version (this thing is far from finished).
I usually make almost all of my variables public and place them in a separate module. That way I can always find them and if I decide to use one elsewhere, it’ll be there. Probably not the best way to do things. If I knew exactly what I was going to do when I started a project, your method is the better way. Unfortunately, I’m a haphazard programmer. I usually start in the middle somewhere and try to work to both ends.
I did rename most of my controls (where it was necessary to do so) but forgot to go back and rename some of the default ones. I’ll place one (as a default) and then figure out what I’m going to do with it later. Forethought on my part would alleviate this problem (and make it easier to later trobleshoot).
I do thank you very much for your input and will take heed of some of your suggestions.
Doug,
Thank you also for taking the time to look at it (and for the accolade).
I really have no idea how long it took to code. Some areas were a snap while others took a lot more thought. Once I was “done”, my son advised me of a lot more nuances with the way they actually work in the field so the workbook is far from complete.
When I was “done” with it as it stands now, the lightbulb went off that I could get rid of all the NYFD stuff and use it more as daily calendar/diary/whatever. I’m working that aspect too and may eventually try to combine both.
Again, thank you both.
I’m pretty much in the same boat as Rich. To my peers, I “walk on water” but here I’m probably not worthy enough to fetch coffee for most of the contributors. I just wanted to throw something out there and get some feedback.
I work in a financial institution, building and maintaining spreadsheets
for traders – I’ve done this for equity derivatives, credit derivatives,
CDO’s, basket products, flow trading, currency swaps, warrants and
commodity trading; right now I’m working on commercial real-estate loan
securitisations. I have seen some of the largest and most complex (read
that as ‘worst’) spreadsheets that can run without crashing, and some of
the best.
Working three floors up from Steve Bullen makes me reluctant to claim
the title ‘Expert’, but I’ve done pretty much everything that VBA is
supposed to do: classes, API calls including the dreaded timer callback,
event-driven programming, database upload and downloads, templated
applications, Excel as a reporting engine, Office interoperability,
add-ins… and all of it fully-tested, with error-handling and real
documentation.
But…
Even if I think I’ve mastered something, someone always comes up with
a better way.
Also, despite the programming skills, I wouldn’t say that I’ve
‘mastered’ the interface: Some of the traders are way ahead of me as
Excel users. Me, I’m a developer – I feel like I’m a Formula 1
mechanic, with these power users as drivers. That being said, I doubt
that there are as many as a hundred people, inside Microsoft or outside,
who can truthfully say that they can make Excel do everything that it
can do, and would never learn anything new.
Meanwhile, banks and brokerages are still using Excel 2000 and 2002.
We’re a conservative lot, and it’ll be a good few years before I use the
XL2007 stuff I’m reading here.
Differ cult question, I think I am an intermediate as I cannot understand anything posted until I spend an hour or two looking at it and playing with it and even then sometimes it is above me. One of the things what helps me most is when one question is answered 3 different ways by three different people. I can kind of look at all 3 and kind of eventually make sense of it. So to anyone who posts a second answer thank you it helps allot.
Its also differcult cause in my office of accountants I’m by far the best but then i am the only one in my office who enjoys it.
I knew opening in IE would cause trouble, I opened in Excel after downloading it. Been there, done that.
I’d advise you to start developing the habit of declaring your variables at the lowest possible level: procedure level.
Have as little global (=public) variables as you possibly can. If routine A needs something from B: pass it as argument(s).
Separate your code into modules which have similar functionality. Declare variables that are to be used extensively within those modules as private variables to those modules.
I tend to have a few global variables, like the name of the application (for messagebox titles and such) and a boolean which is set to true if my application has initialised succesfully. That way I can test if my app has lost state and if so, redo the init.
Separate your code into smaller chunks within functions/subs with a simple, straightforward goal. If you do that, you’ll see that the number of arguments you need to pass to the functions/subs isn’t very large, which avoids having to have public variables too.
[stepping off soapbox]
Following up JKP’s comments (without looking at the utility in questions).
Get in the habit of declaring the variable in the procedure where you need it when you decide you need it. Declare one variable per line, don’t think it’s saving you anything to use commas to save lines of code. Changing multiple line declaration statements is a pain.
Get in the habit of naming controls and other objects when you create them with descriptive names, and change the names if the descriptions change (this goes for variable names also).
Another good habit: if you open a block of text, write the closing of the block before the internal workings of it. If you type an If statement, write your End If, then the stuff inside. If you type With …, type End With, then the stuff inside. Same with For[Each]/Next, Do/Loop, Select/End Select, etc.
Create an IF block rather than a one-line If statement. It’s easier to follow and easier to change later when you remember something else.
Put one statement per line. Sure, you can string a bunch of commands together with “:”, but this makes it harder to read and harder to change.
Don’t comment something out that you might need “later”. Later almost never comes. If you don’t need it today and maybe tomorrow, delete it now.
Nigel –
Don’t compare yourself to Stephen. The scale doesn’t go high enough for him; he has his own category. He’s forgotten more than we’ve all ever stolen from him.
You would think that being an advanced user I would be able to solve my own problems….cannot seem to resolve an issue with Excel 2003 Range(“F22?).Formula=”=” won’t recognize my formula. I bought your books and have had great success with everything but this current issue of mine. If you get a chance, email me back and let me know if you are interested in seeing the full problem. My project is to import (in batch) xml maps from xml files and compare/evaluate them all side by side in a worksheet. My formula doesn’t function when I use it; all other cell manipulation for the Range works fine – just not my use of .Formula.
Thanks for the great books, I have hundreds of Excel solutions should you come up short on one. Send me a problem statement and I will look to see what I can help with.
Jack,
Here’s a shot in the dark since I don’t know if this is your intention. If you are just testing the leftmost character you would need Left(range (“F22?).Formula, 1) = “=”, I think.
Brett
Thanks for the stab at it Brett…fortunately, it turned out to be that I kept missing an internal quote (you have to quote the internal quotes to keep them in tact) and the formula wouldn’t work until it was formatted in such a way that it was actually a formula. Pretty simple solution after all for a mega-formula with lots of quotes. Everything works great…now I am trying to unload all of the memory usage so that it works faster. If anyone needs a tool with code to auto-extract xml files into a workbook and perform side-by-side comparisons just email me and I will send you the workbook.
Dick, a nice range of abilities is addressed by your work here. This is my “go to” site for excel tips. Don’t change a thing!
A year later …
Jan said “That way I can test if my app has lost state and if so, redo the init.”
If the app loses state, does it stop running? If not, how often do you have to check for loss of state, or in what conditions, and how do you know how much to reinitialise?
TIA
Patrick