No Topic

Weekends are kind of slow around here, so let’s try this: A post about nothing.

This is where you, a reader, post. Ask questions. Suggest ideas for posts. Tell us how you use Excel. Tell us how you first met Excel. Anything goes as long as it’s possibly marginally related somehow to Excel, people who have heard of Excel, or people who have spent time in a cell.

You can even ask questions like, “Hey Dick. how’s that eye surgery working out?”

Posted in Uncategorized

57 thoughts on “No Topic

  1. I need some advice. I have a good deal of experience, and knowledge, in financial services, mainly in fixed income. However, I am very interested in programming VBA and C#. I was thinking of making a career change and becoming a full time VBA developer for a trading desk in an investment bank. Is this a good move? I’m worried that the industry will move to VSTO, I just started learning C#. I’m afraid of leaving my financial experience.

    Also, how do I get better at programming? What’s a good way to practice? It’s very hard to think of new projects for myself, especially in C#.

  2. I’d appreciate if one of you skilled writers could shed some light on why VSTO is regarded as more Professional than VB/VBA. Is it safer ? More events ? Larger available object model ? Compiled code ?

  3. John, I first got to reading your stuff when I needed to upgrade my Excel skills. I had just got a new position that required analysing sales data for the marketing manager and monitoring inventory activity. Excel was what would interface with the legacy business system we use.

    I went into the local book store and bought the biggest, fattest book on formulas I could find. None of that “dummies” stuff for me! Next year, I decided I needed to learn VBA so I bought the biggest, fattest book on VBA I could find. Funny how your name was on both those books. I think my boss is hinting about more simplified presentation in the spreadsheets. Maybe I should go to the book store and buy the biggest, fattest book on charting I can find. Somehow, I think your name will be on it.

  4. LOL @ Omar! But I’m definitely looking forward to reading your new books John…

    Hi guys, my name is Mike Rosenblum. I’ve not participated much here to date (actually, I think that my first comment might have been yesterday?), but I have come by here from time-to-time in the past to read some of the outstanding contributions. This really is “the place”, but I think you guys all know that — and help make it that!

    I am addicted to Excel, as are all of you, and I’ve put a fair amount of emphasis on .NET, so I may be able to add some perspective. In particular, I think I could be able to be of some help to MacroMan and Harold, who posted some Q’s above.

    I am somewhat verbose, for which I apologize in advance… But hopefully some of what I can contribute will be of value to somebody. I’ll comment to MacroMan first and then maybe later today or tomorrow, I’ll try to reply to Harold.

    Thanks for having such a great site Dick, and everyone else who contributes…

    – Mike

  5. MacroMan,

    I would get a good VB book and start learning. But you will quickly see that the complexity is high. First the language — although clearly better than VBA in many ways — is more complicated with certain issues (strong-typing, object release, etc.) and just when you start getting your head around that, the deployment issues really raise their ugly head. (If you want to get a rough idea of what’s involved, you can have a look at my tutorial, here: Automating Office Programs with VB.NET. I have an VB.Net Office Automation FAQ as well, which has a number of useful links.

    The other issue for moving from Financial Services to VB.NET or C# is, well, the salary. In financial services the expected salary level definitely higher, and let’s say you have 10 years experience. Then you switch to programming and you have some years of VBA skills and no .NET skills… So you’re roughly at the level of a 1st year or *maybe* a 2nd year professional programmer. So you are trading 10 years experience in a higher-paying industry for 2 years experience in a lower-paying one. This could really hurt…

    I’m not saying that it can’t be done, but I would definitely not do is try to “go out on your own”. If you are serious about this, then I think your best bet would be to talk to the programming guys within your current firm. You might even be able to move over without a cut in salary (not likely, but maybe possible). Start with a discussion at least. Or maybe work with them informally, be their beta testers, etc. They’ll love a financial guy with programming skills. The next best idea might be to try to get a job with a financial data service provider, maybe Reuters, Bloomberg, FactSet or the like in order to leverage both your financial and programming skill sets. (Use a headhunter, for doing a job hunt on your own while still employed is just too hard.) Lastly, consulting firms that focus on MS Office development for the financial services industry would be another thought. (These you might want to approach directly.)

    But if you want to “make the leap” as a .NET programmer, then I think you’ll need to start by reading a .NET book! :-) It looks like you have started with C#, which is fine. The “real programmers” will definitely respect you for using C#. But Office development is quite a bit easier using VB.NET, especially if you are comfortable with VBA/VB6 currently. I would focus on VB.NET and read/learn C# on the side. But keep in mind that .NET and Excel is not an easy combination because of cross-version compatibility issues, security/trust complexities, and deployment. I’ll discuss these issues later today or tomorrow when I reply to Harold’s question, above.

    – Mike

  6. Macroman,

    1. Keep your job.

    2. Start a website that teaches C# or VB. No better way to learn a topic than to try to teach it.
    Possibly angle your classes from an Excel programmer’s perspective. Each lesson should be task oriented and should focus on the why’s as well as the how’s.

    3. Think of few a common problems and write some freeware apps that solve them. Offer those on your site.

    After about a year, you will have learned a ton, and your site will start generating consulting leads.


  7. Hui: The eyes are great. The left eye seemed to get worse over the first few days, but that was temporary. As of last Thursday, both eyes were 20/15 and I don’t think much has changed since then. My eyes are a little sensitive to the sun. I bought some Ray-Bans – it’s the most I’ve ever spent on sunglasses. I hate owning expensive sunglasses because I think I’m going to sit on them or something. They said I may have a “halo effect” at night, which I just noticed for the first time the other night. But it’s not bad or distracting or anything. Someone asked me if it’s the best money I’ve ever spent. Earlier this summer I bought StarCaddy which was a pretty damn good purchase. I’d say StarCaddy and the eye surgery are tied for the best money I’ve ever spent.

    What’s the best money you’ve ever spent?

  8. MacroMan
    C# is a great language, I’ve some book reviews on my site:
    check out Andrew Whitechapel and Eric Carter near the bottom of the page both have good Excel/C# content. You probably need a reasonable idea of C# to get the most from the books.

    I’m not seeing much movement to .net in the Excel job ads in the UK so VBA and Excel is still a very powerful combination careerwise. Another option might be C++, thats still popular.

    The least pain way to learn C# would be to go on a course, but MS press do some good self study books too.
    I have found working Excel with C# is quite painful after VBA.

  9. Harold,

    You asked a few different points related to VSTO/.NET versus VB6/VBA. Simon summed it up concisely when he said “I have found working Excel with C# is quite painful after VBA.” I would have to agree 100%.

    I should probably just stop there, lol, but I’ll address your questions the best I can one-by-one…

    > “Why is VSTO regarded as more Professional than VB/VBA?”

    I personally don’t think that VSTO is “more professional” than VBA. VBA can be “just as professional” in my opinion, but it depends on how it is used. VBA is derided for two unfair reasons: (1) VBA is still referred to as a “Macro” language (which it most definitely is not anymore) and (2) because VBA uses P-code instead of fully-compiled native code, there are those who will deride it a “Scripting” language, which it isn’t either.

    What makes VBA truly unprofessional, in my opinion, is when the coder uses late binding everywhere, makes excessive use of Excel’s globals such as Range() or Cells() without explicitly qualifying to a specific worksheet, and/or omits using ‘Option Explicit’.

    In .NET these globals are no longer exposed (thank goodness) and so one would have to specify the Worksheet in question when looking to access the Cells() or Range() methods. Overall, using .NET imposes a more explicit coding style, unless one turns off ‘Option Explicit’ and ‘Option Strict’, which is generally a very bad idea.

    On the other hand, some VBA’ers will miss their handy Range() and Cells() shortcuts, omission of Option Explicit and frequent use of late binding. But I’m willing to bet the majority here are already using VBA in a rather “strict” manner, using Option Explicit always, using Late Binding only when necessary, and avoiding reliance upon the Excel globals.

    So, no, I do not think that .NET or VSTO is “more professional”, although, I guess the die-hard .NET crowd might disagree. .NET certainly does scale better for larger, more sophisticated projects. But for what one generally needs to do for MS Office, VBA and VB6 does a very good job.

    > “Is it safer ?”

    It is in the sense that .NET code has to be trusted in order to run. VSTO takes this even further and will not even run in a semi-trusted state — all VSTO projects must be strong-named, etc. So you can’t just put a VSTO project within the ‘AltStartup’ directory, as you can with an XLA and expect it to run. If it is not digitally signed, it won’t load.

    But this security makes deployment a challenge. Maybe the “professionals” do not find it much of a problem, but for the rest of us it definitely is. Compatibility across multiple Excel versions is another serious headache for .NET. I personally think that the best solution is to compile different versions for each target: Excel 2002, 2003, 2007, etc. Binding to a lower version and running on all higher versions (as you could do with a VB 6.0 Add in, for example) is theoretically possible, but it seems to be difficult at best. I’ve not kicked this stuff around myself yet — I’m just not quite ready to “go there” — but I’ve read enough to know that this is not easy.

    Here’s an article from DevCity, where the author creates a local Interop assembly by using TlbImp.exe to allow for multiple-version compatibility using early binding for Excel versions 9.0 and higher: COM Automation across multiple COM versions. I’ve not tried this myself, but one can see immediately that this is not nearly the same as deploying a VBA or even a VB 6.0 solution!

    > “Compiled code?”

    Well, .NET produces intermediate language (IL) code which is compiled to native code when first loaded, or when a section of code is first accessed. Fortunately, the time to execute the just-in-time (JIT) compilation is not really noticeable. The actual running time for looping or sorting arrays, or other basic operations when clocked against VB 6.0 code is pretty much identical, or actually infinitesimally faster in my own testing. However, when calling the Excel object model (which is really what we care about!), then the .NET Interop has to translate all calls to-and-from COM, because Excel is COM and .NET code is non-COM (“managed”) code.

    I’ve not actually tested the speed in this case. When doing out-of-process automation, I suspect that one would never notice because out-of-process automation code already runs around 50x slower than in-process VBA code. I’ll guess that the overhead of the Interop might add something to this, but I doubt that it would be very noticeable.

    You can also use .NET to create a managed DLL which is exposed to COM. Functionally, it works just like a standard COM add in created using VB 6.0 — you can even create an automation add in as well. I have not tested the execution speed in this case, but when run as an automation add in the following article from (I guess this is you, Simon?) shows that UDFs created using C# or VB.NET will run about 8-9x slower than the same UDF created using VBA or VB 6.0. If this is right, this is pretty painful.

    > “More events? Larger available object model?”

    VSTO does add a few new controls which are very good for data binding to an external database. Drag-and-drop, very nice. There is also easier access to Smart Tags and Task Panes. And yes, there are a couple of more events such as within the NamedRange class that can detect some events that are not directly available in VBA/VB6 — although you can create them all if you wanted to (I have done so myself a few years back using VB 6.0). Anyway, not many new events worth worrying about, but the controls are nice if you are doing a lot of database access. Still, overall, there is not a lot that is compelling here.

    What definitely *is* nice about VSTO is that it allows you to take worksheet controls and drag-and-drop them onto the worksheet and the code-behind is not VBA, but is VB.NET instead! (Or C# if you want.) It really is amazing. Here you are feeling like you are using the Excel/VBA interface, but when you right-click on the worksheet tab and choose ‘View Code…’ the result is VB.NET. This is really nice if you appreciate using .NET code.

    However, the versioning issues and deployment complexities really do render the whole VSTO thing all-but-unusable to us VBA/VB6 guys. I see it as a glimpse of the future… some day we’ll have “VBA.NET” if you will, and it has the potential to be incredibly nice. But, right now, the typical MS Office programmer will find VBA and VB 6.0 a much easier approach.

  10. Mike Rosenblum, Mike Alexander, and Simon Murphy, I appreciate your comments.

    Mike R., great advice. I’ll take your word that is easier to use for office development but I wanted to diversify my skills. I want to also be marketable to program stand alone windows applications, and it seems that the industry looks for C# developers for this. Also it will also be easier for me to use other languages in the C family of languages. As a personal preference, I just like the C# syntax better.

    Mike A., also great advice, but maybe too advanced for me at the moment. I tinker with snippets of code, familiarize myself with the .Net library, and object oriented concepts, but is it important to read up on design patterns and data structures? Or is it better to just keep reading source code and experiment?

    Simon, C++ is an option but I hear that C# is the future. If you’re building applications, and not operating systems and video games, it is just as powerful as C++ and a lot easier.

    Thank you all again for your response.

  11. I’m an accounting professional and I spend a big chunk of my day working with Excel. Despite the time I’ve spent working with spreadhseets, and I’m always humbled by the great info I find at this site and the sites of the contributors here. Thanks so much to all who play a part.

    There are two Excel-related items I’d like to see more info about. The first is on spreadsheet design and layout. I see a ton of stuff on specific formulas and marcos, but not so much on layout. What are some of the more challenging layout issues you’ve faced and how did you solve them?

    The second issue is I’d like to know how people use Excel to analyze resonably large data sets. I often deal with time series data, and I find that using Excel graphs is a great way to quickly spot trends, spot outliers, and find other problems with data. I know how to create basic graphs, but I haven’t figured out how to do it quickly. How do you plug different variables from a time series into the same graph? Pivot charts would probably help, but I find them a tad confusing.

    Thanks again for creating such a wonderful place.

  12. I’m an affordable housing developer in Portland, Oregon. Much of my Excel work involves pro formas and operating budgets for yet-to-be-built rental housing. One of my bigger work challenges is linking all the line items in my workbooks to those in workbooks of funders who break down costs by different line items. Not only do my ever-changing budgets (construction costs are inflating here at something like 10% a year) have to balance, but I have to make those balances flow through to these funders’ workbooks.

    The biggest workplace challenge that I haven’t solved yet is how to simplify my pro formas and other Excel projects enough so that, if I’m hit by the proverbial bus, others at my workplace will be able to use them. I’d be interested in hearing how folks out there deal with being the only advanced Exel/VBA user in their workplace, and how much time you spend trying to fool-proof workbooks so that others could use them with the same utility as yourself.

    I love programming. The only language I know now is VBA. I’ve recently finished my biggest VBA project and I’m thinking once again about whether to learn .Net or VB. After reading the above, and other similar discussions in the past, I’ve decided to go with VB. I think it will allow me to do what I want to do – write programs for myself and maybe a few others – without having to spend the time necessary to figure out all the complexities involved in .Net code. Also, the VB IDE runs a heck of a lot faster on my 4-year-old laptop. I do really like some things about the .Net IDE, mainly that there is so much contextual info, but I can’t stand the seemingly random help.

    The VBA project I just finished was written at home over hundreds of hours but I use it mostly at work. It’s affectionately called “FlyBar.” It manages all of my addins. When any workbook opens, FlyBar checks to see if the workbook has an addin or menu and, if so, it opens and/or creates them. It also captures all application-level events, checks to see if the workbook has such an event, and if so, runs the event (I thought this would slow things down, but it doesn’t seem to). Before FlyBar opens a workbook’s addin it checks a network location for an updated version (I think I got the idea for this from Charles Williams).

    I have 2 questions about this: 1) Does that description make sense to anybody? and 2) does anybody else use something like it?

    Thanks as always, Dick, for a great site. I barely golf, but my dad, at age 76, is hitting the ball farther than ever. He’s teaching my 9-year-old, and it’s looking like she’s inherited his talent. I’m the one with the Palm Pilot, though :).

  13. Hi everybody,

    Hats off to the excel gurus !!!!!!! I have a problem (ought to be a small one, I guess, for you people……….).
    In India we use “ITNS281?, a challan, which is the legal document, through which the tax is paid into the government account. Tax can be paid either through cash or cheque or by debit to the bank account of the payer. It is printed on the challan as “Paid in Cash / Cheque / Debit to A/c”. If Cash is the medium of payment, then the other two options are struck off (like “strikethrough” effect in excel). That is failry simple, off course, if the challan is filled manually. But I have created the form in excel, so that if one is to deposit many challans, the only job is to select challan number from a validated box and the challan will be filled.
    My problem starts here. If mode of payment is mentioned as “Cash”, then the other two options should get “strikethrough” effect automatically. Off course, that can be achieved by simple conditional formatting, if I mention all the 3 options in 3 different cells of the Challan. Out of cynicism I tried to write the complete line in one cell only. But I just noticed that even though you can “strikethrough” some of the words in a single line mentioned in a single cell, but when you refer to that cell by some formula, the text is just plain with no strikthrough. Also the same was not achievable, if I write the same within the “if” formula, because then the “Format > Cell” option becomes inactive. Still trying but with no success, any help.

    Thanks and Regards

  14. Dick, The Best money I ever spent ?
    Probably PUP v2 and the source code, a few years back now

    Have learnt more from that code than I could have otherwise and PUP v6 – use it every day


  15. In the 90’s when we on vacation trips sent postcards (by snailmail) to friends I usually signed the cards with ‘Your cellmate’. One friend was not at home and a neighbour to him took care of his mail. When he got back no neighbour said hello and after a while he find out that everyone did ‘know’ that he has spent time in the jail!

    The best money I’ve ever spent is for: Dishing machine

    Mike – thanks for the link to the DevCity article.

    Kind regards,

  16. Doug,

    I think you are probably making the right pragmatic decision to go with VB6 at this point. But I would still keep .NET is mind as well. .NET is a fantastic language, and I would keep a .NET book by your bedside. Eventually it will grow on you in a big way… but as a practical matter it is tougher to deploy MS Office solutions with it.

  17. Thanks Jon,

    Yes, it is not really a show stopper. I am doing the job by putting the same in different cells. But I really want to know whether there is any practical solution.

    Thanks and Regards

  18. When I bought StarCaddy, their website said that it was not cheating. I foolishly believed them instead of investigating for myself. Now the site says that it is against the rules of golf. The USGA says using GPS to get distance violates rule 14-3, but that if I use GPS in a round I still have to post the score for handicap purposes. I don’t play tournaments, so I guess it’s okay if I use it. Or is it? I really don’t know anymore.

  19. MacroMan
    Yes C++ is pretty retro, but its still the only way to write fast UDFs. C# writes very slow ones (yes Mike Codematic is my site) If anyone can point out where I have gone wrong, or code up the last non zero test formula in, I’d love to hear from them. As a matter of interest the VB6 one was to native code and was still no faster than VBA.

    Dave – There are some spreadsheet design slides on the codematic site from a presentation I did at the Excel User Conference.


  20. Hey Simon,

    > “As a matter of interest the VB6 one was to native code and was still no faster than VBA.”

    In general testing, I find that subs running from COM add-ins made with VB 6.0 will call the Excel object model at exactly the same speed as VBA subs, but will call non-Excel calls such as looping, array access, string manipulation, etc. at about 2-3x faster because VB6 is running native while VBA is running in P-code. (Calls to Excel are the same because the Excel object model methods are fully compiled.) However, in most Excel subs, calls to the object model dominate and so COM Add-ins running subs will run only about 15% faster than VBA on average.

    I’ve not tested Autmation Addins in this manner, per se, but in theory it should be the same. I would think that UDFs might rely on more math and less calls to the Excel object model, but maybe not. It depends on the function, I suppose.

    But one thing that I have definitely noticed is that VBA UDFs seem to have some sort of overhead or lag when called. Normally this is not noticeable, but I once made a non-Volatile =Rand() UDF and was shocked at how slow it was, even though the code within the Function was just a simple LCG. Use of =Rand() or =Rnd() functions are different than most in that one is quite likely to need thousands of them to do a simulation. So this “overhead” or “lag” I was seeing was a big problem. However, when I put my =Rand() UDF in an automation add-in instead, it flew. I didn’t actually time-test it because I didn’t need to; it was dramatically faster by eye. (Hmmm… on the other hand, since an LCG is a pure math formula with no Excel object model calls, I guess a COM UDF should be 2-3x faster than a VBA UDF in this case? So maybe there is no “lag” or “overhead”… Not sure.)

    > “If anyone can point out where I have gone wrong, or code up the last non zero test formula in, I’d love to hear from them.”

    I don’t see any reason to doubt you! The Interop is probably worse for UDFs than for subs because one will have many, many small UDF calls throughout the worksheet, whereas a sub is likely to have more loops and processing going on per set of arguments passed in.

    The only way that I would think that your timing could be off is to make sure that the UDF was loaded first by calling it before any time-testing has occurred. This way the JIT compile has definitely been completed, etc. But, for that matter, Automation Add-ins have the same “load on demand” approach as well… VBA on the other hand, should not have, I don’t think.

    I have no doubt about your result though. However, some data-types might pass through the Interop faster than others. In theory, Doubles are the same in both languages, while Dates are completely different, so Dates and possibly Strings could be slower than Longs and Doubles. Also, passing in a Range would require an RCW, and so I suppose could be relatively slow as well, I guess.

  21. I thought it would be interesting to list 10 features that havent changed in 10 years of
    Excel (97-2007) but which should have improved atleast a bit in 2007

    Ex :
    1) Data validation
    2) Custom Views
    3) Data – Form ( Does any one use this feature…)
    4) Data – Table (Again does any one use this …)
    5) Data – Text To Columns
    6) Goal Seek
    7) Senarios
    8) Paste Special (marginal changes – Column widths…)
    9) Format Cells – Custom (accesibility to the Dialog)
    10)Advanced Filter

    There are a couple of features that have changed marginally in 007
    a) Names…I was hoping that Excel 2007 would have a name manager comparable with JKP….and a Dynorange feature (define dynamic names)
    b) Charts ….No new chart types in 10 years….amazing


  22. Hi All,

    First of all thanks to all the great writers of this blog.Daliy dose of excel is Daliy Read for me :)
    I need some advice of excel experts here

    I am a Software Engineer working in one of the software firms in India .I was working with various technologies like VC++, VB etc and now working with VBA for last 2 years for a leading fortune 500 client of my current employer. I took this project as challenge as I am not biased to any technology and was always interested in learning new technologies (none of my colleagues was willing to work in excel).Now my company consider me as Excel resource and they always find some new project which will be single handedly done by me.Eventhough I am not a great excel programmer I always manages to find solution with help of great community like you people .I usually post doubts in forums and regular visitor all MVP sites. Now I love VBA and I am confident of doing the tasks. But I am little concerned .My friends are saying that for career sake i must stick to technologies like java or .NET. But I love VBA and Excel and want to become an excel expert like you all .But some of the facts I found are

    1) All excel experts (or Excel MVPS) are associated with a domain. I mean they are not programmer like me doing full time VBA. They are either engineers or accountants using excel to enhance their business. So do I need to be from some background like that to have a career in that? I am computer science graduate and has only 3 years of software industry experience

    2) Is it possible to build a career with VBA alone? I have seen lot of excel vba jobs in UK .But in India I don’t see any companies call excel vba professionals. Some are there, but relatively small companies .So there is much option for switching career as I am working in a pretty good company.

    3) All of excel MVPS are running company of their own. Is this the only way to make a decent income form VBA?

    4) I think most of the excel MVPs are above 35 :). Is the excel knowledge can be gained only through ages :)

    Please advice on my thoughts



  23. @Xcelion:

    “”Another kid, younger and a little geeky looking, runs up
    to Dade and The Phantom Phreak.

    dudedudedudedudedudedude… I gotta…

    (slaps Joey)
    Joey, Joey…

    What? whatwhatwhat?

    One more “dude” out of you and I’m gonna slap
    the shit outa you, okay? Now I’m trying to
    save you from yourself but you gotta stop
    letting your mama dress you, man!
    (To Dade):
    Check it…

    Phreak starts to hand Dade a flyer.

    I need a handle, man. I don’t have an
    identity until I have a handle.

    You know, you’re right about that.
    (to Dade)
    Check it, Friday.

    Phreak hands Dade a flyer for Cyberdelia.

    Alright. How about the Master of Disaster,

    You’re hopeless, man, utterly hopeless.

    Phreak walks away.

    Ultra Laser.
    Doctor Doom!””

    [borrowed from

  24. 4) Data – Table (Again does any one use this …)

    Yes – can be useful for sensitivity analysis – especially when combined with conditional formatting.

  25. Hi Sam,

    Re: “10 features that havent changed in 10 years of Excel”

    I have never used the following features.

    Data – Table (Again does any one use this …)

    Infact I just noticed that there is any such option. Ooooooooopsss……

  26. Yes. I also recommended it to my colleagues (when I still had colleauhes), to apply their spreadsheets to a batch of data without using VBA.

    Niek Otten

  27. Xcelion –

    1. I’m not associated with any domain. I have expertise in data presentation (making Excel charts do more than they were designed to do) and automation within Excel and between Excel and other Office apps. I’m more of a generalist problem-solver. I’m sure other MVPs and successful Excel programmers may have came up through a particular domain, but it’s not a requirement.

    2. I and many others are making a living doing Excel/VBA.

    3. I’d guess that less than half the Excel MVPs are running their own companies. I only started after being laid off after a 15+ year engineering career. I needed a change.

    4. It’s not just Excel knowledge. It’s experience solving problems, and it’s time spent gaining credibility and name recognition.

  28. Data Tables – This nominally can be a useful technique, but I have always found it rather obscure to set up when you can just as easily write a formula with mixed relative/absolute references to the top row and left column of a grid, and fill the formula into the grid. This formula can be used for sensitivity analysis, you can use more than two variables if you allow for multiple header rows and columns, and the conditional formatting works as well as in a table.

  29. Thanks Jon .Thanks a lot for your advice
    It’s great to hear from a person like you
    I am a great admire of you and a regular visitor to your site

    BTW your “Speedometer Chart” is awesome :)


  30. sam, that’s an interesting list.

    Did you know that you can’t create a custom view in XL07 if your workbook contains a Table? It would be ideal to create custom views of various filter sets in a Table, but it’s not possible.

    Apparently, quite a few people use that DataForm — at least judging from the interest in my Enhanced DataForm. I could never understand why. It’s so much more efficient to just enter the data directly. In XL07, that command is now relegated to the “command well” so it may as well have been removed.

    BTW, Paste Special does have a new option button: All Using Source Theme.

  31. Sam,

    I’m surprised given the audience here that no one mentioned the #1 feature that hasn’t changed since X97, and probably never will, the VBA IDE. Scroll wheel, anyone? Also worth mentioning, is the options dialog. I can hardly say the 2007 setup is any better. They did move the tabs from the top to the left side, but it’s still a jumbled, cluttered, daunting mess.

    Now, how about the top features they should’ve left alone but didn’t. My numbers 1 and 2 would be that horrible office clipboard and those crazy menus self-rearranging menus.

  32. Jon, about Data>Table,

    Your suggestion works fine where you have one formula.
    I was referring to the situation where you have many formulas on maybe several sheets to get to an answer. My example is a Universal Life Insurance cashflow projection of 600 months (600 rows of 150 columns), with cost deductions, intrest additions, irregular payments and actuarial formulas involved, on 12 sheets.
    Normally an actuary would develop such a calculation workbook to handle one case; using Data>Tables it can now be applied to a batch of records and generate a table of results, without using VBA. It would run for several hours.

    Niek Otten

  33. I recently purchased the “SkyCaddie” – same basic effect st the StarCaddy it seems.

    Anyway, the USGA allows them in official competitions if the course has a local rule saying it is allowed. Similar to laser range finders now – same deal – if local rules allow. The USGA is starting to accept these, as they are starting not to mind if you have a yardage – to which I say it’s a bout freaking time. I heard they are against (things I didn’t even know existed) things that tell you elevation change and wind speed.

    As far as your eyes go – how is working in front of the computer all day? Any worse/straining than earlier?


  34. Sam,

    It would be great to have an updated IDE, but FYI a Belkin optical mouse will enable you to use the scroll wheel in the IDE.

    So why don’t they upgrade the IDE? Is it the obvious? That they want programmers to use VSTO so they can sell more Visual Studio IDEs?

    By the way this site is great, I learn a lot from you guys. Dick, maybe you can add a section where we non MVPs can post questions all the time?

  35. “So why don’t they upgrade the IDE? Is it the obvious? That they want programmers to use VSTO so they can sell more Visual Studio IDEs?”

    I think it’s more that MS is not interested in further development of the old style VBA IDE, so they decided not to expend the resources on it. The desire to push us to VSTO is less pronounced. They will admit (if pushed) that VBA works just fine with most things you’d want to do with Office.

  36. Jon,

    About Data>Table I agree with Niek. The Data>Table construct enables you to maximise the number of calculations for a minimum of formulae.

    I found a good way to use Data>Table in J-Walk’s Excel 2003 Formulas (p. 262-263) – for me it has been worth buying the book just for those two pages.

    J-Walk shows how to summarize a large flat table by using Database-functions in a Data Table. As you know D-functions refer to a criteria range, but I let this coincide with the two top left cells of my Data Table, making the set-up process easy and simple (the trick is to use only one criteria per Data Table).


  37. Jon,

    The problem I found with Data Tables was that it used to crash my Excel 2000 if I some one accidently edited the {Table} Formula….Havent tried it in 2003….and I just stoped using them in favour of formuals with mixed relative and absolute reference as you mentoned…

    Didnt know about custom views not working with Lists….cant think of why they shouldnt…

    I agree with the Data–Form… I just prefer to enter directly in the cells.

    The Data -Consolidate hasnt changed either nor has Data -Group and outline …have they ?

    Regarding Paste Special…

    I was hoping for the following in 2007 …

    a) Paste Special with combinations (like the ASAP one)
    b) Paste Special – Formulas – Should ask “Shall I paste formulas relatively” or “shall I paste the exact formula” (like your PUP utility – Copy exact formuala..)
    c) Paste Special – Row Height (Just like column width – Andrew it developing a pastespsecial addin which has this feature)
    d) Get Paste Special to work on a filtered table
    e) Paste Special – Multiple items in one Field on a filtered table…

    “All from Source Themes”….doesnt get me excited…


    Top ten things that they should have left alone….

    1) Menus and Toolbars….2)Menus and Toolbars…..10)Menus and Toolbars……I want my menus back…I want my menus back… :-)


  38. I only learnt about Data Tables after reading earlier this year although I’ve not since put any of it into practice.

    My comment: As others have said, it will be an age before my company upgrades so I hope this site will continue to show tips & methods I can implement in Excel 2003. Of course, as soon we do upgrade I can stop worrying about the giant step change and learn to love Excel all over again.


  39. Re Eyestrain: I never noticed eye strain prior to the surgury and I don’t notice any now. So I guess it’s the same. Some people I know have a real hard time looking at a monitor all day, but for some reason I’m not one of them.

    Re nonMVP questions: First, note that not all the authors are MVPs. Also there are links where you can send me an email that I will put on my pile of things to do – and never get to it. Maybe I should just continue what JWalk started here and have a Weekend Forum thread every Friday for general questions, discussion, etc.

  40. Are you guys using Beta2? The autocomplete for worksheet functions and defined names is quickly becoming my favorite new feature.

    There are still some old key combinations that I can’t seem to shake. Alt+I+W to insert a worksheet, followed by Alt+O+H+R to rename it, are two that I keep falling back on. When I use those, that floating thing should tell me the new keyboard combination so I can read it every time I use it.

  41. My intro to Excel came when asked to calculate averages for my boss’s skittle team (like U.S. bowling only 9 wooden pins and stronger beer). Who knew that a few weeks later I would have to do averages using Excel as part of a job interview, yes I got the job and I’m still here and still loving Excel.

    I’m now an ‘advanced’ user according to Aaron Bloods user scale (, so I often get asked questions at work. It’s amazing how under used Excel is, I keep telling everyone how great it is for learning about other programming languages and computer logic generally, everything is so neatly contained! (unlike Word, yuk).

    My best bit of work is a fully functioning WW2 Enigma machine, that really stretched me but was great experience. It’s the only way to really learn – set up a big challenge then keep chipping away (here’s where I quote that scene from “Pale Rider” with Clint and the big rock).

    I’ve written a book for Excel beginners through to VBA (as it’s not published yet I guess it’s just a bunch of Word docs not a book). Some of you guys get credits of course. As I’m a user not a writer it may never see the light of day. But you never know.

    One question for regular contributors to this blog, in light of the major changes in Excel 2007 – how come Bill doesn’t just invite you all over to get opinions and feedback first hand rather than just unleashing this stuff on an unsuspecting public.

  42. > “When I use those, that floating thing should tell me the new keyboard combination so I can read it every time I use it.”

    Yes, I have definately been thinking the same… but I’m guessing that they are aware of this idea? It’s hard to know what stage they are at internally — the Beta 2 is already a stale snapshot. I’ve heard talk about the “Technical Refresh”, I think the rumor is that it’s due out this month (August).

  43. gruff999: Bill does invite us up. MVPs attend a summit every 18 months or so. Last summit was pre-alpha time frame so it was the perfect time to give advice on the product. Our advice is a drop in the ocean of all the advice they get, so it seems like a lot of what we say falls on deaf ears. Nevertheless, it’s great to get to talk to the Excel product team. It’s also great to see Jon Peltier talking to the people who own charting – makes you think something might sink in.

  44. Hi everybody.
    It is my first written communication to this blog. I have created a small add-in (somehow…………. at last). But I don’t know how to make it a 30 day trial version, so that after 30 days anyone using it is required to register it or purchase it etc…

    Thanks in Advance.


  45. Hi Amar

    There is no built-in way to do that, and Excel files are not safe as in Safe. Let the evil parts of your creativity play with the scenarios for a while. I believe that putting date somewhere, disguised, in the registry on first run is a pretty common way.

    Best wishes Harald

  46. Hehe… Did anyone else notice that this is cataloged under the tenth hole tracker?? Anyways… Back to figuring out my golf handicap!

  47. I would steer clear of software development. They’re giving all of the work to people in India and China and Vietnam and everywhere else that developers quote insanely low prices. Idiot CEO’s in this nation (USA), only thinking of their own wallets – as usual, have dumped very talented and experienced programmers onto the streets in lieu of these low-ball development groups from overseas.

    Its this outsourcing disease that is destroying the United States. Anyone with any sense of the future will find a new career in something they CAN NOT outsource. This is what I am planning on doing.

    Enough with competing with SQL Server DBA’s in India asking for 9.59 per hour when here, in the USA, $100/hr is the average for that position.

  48. I’m new here on the forum, found it by searching google. I look forward to chatting about various topics with all of you.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *