Oz du Soleil
Data Management and Excel Specialist. Your Data Marshall!
@awolfe57 Andy, thanks for the comments.
VBA does take us into a whole different world, as I described in "6 Reasons Not To Use VBA" http://datascopic.net/5-against-vba/
Where were you when everyone was beating on me for that? LOL!
I use VBA as a last resort for the reasons you describe. However, more and more over the past few years VBA has been a regular part of my projects. People are trying to free themselves of reliance on IT and they start creating their own desktop solutions in Excel. Now, the problem is that many of them are still beholden to their companies to get them upgraded from 2003 and 2007 so that they can use things like PowerPivot (2010), Data Models (2013), etc.
I'm also with you, in that, I love to teach and empower. VBA has a vast learning curve and is really only for the folks who are going to be using it a lot.
6 days, 12 hours ago on 3 Reasons To Use VBA: It Tames Beasts!
@CHatmaker Thanks so much for jumping into the conversation. I haven't heard form you in a while.
You see that even I agreed with RStadther--except for coming onto someone else's turf and calling something "dumb."
Referring back to the "or be careful" piece in the blogpost title, I think we all agree with each other. There are a lot of reasons to be careful about using VBA. In no way do I suggest VBA is the devil.
#3 is a warning because there's no UNDO. Yup. We're only human. And every time I get ready to test some VBA I get everything set up in case the VBA code is wrong, as a last resort, I can close the document, "do not save", reopen, and be back where I was before running the wonky code.
As you, I and RStadther agree, there are ways of dealing with all the #2 thru #5 things that I mention. There is "responsible coding." And there's a helluva learning curve to get there.
With #1. I fully agree that we shouldn't refuse to do something just because someone might leave. My warning is that freelance VBA coders don't grow on trees. I've seen people whose spreadsheets have been broken for years because the VBA coder is long gone, something broke, and the spreadsheet skill kinda works but not like it did. And there are 2 problems:
1. Finding another VBA coder who can take on the project of fixing someone else's code. Most of the VBA coders whom I know have day jobs and aren't looking for weekend work.
2. Coders all write in their own different styles (not only in VBA, but any kind of code: CSS, Java, Python, etc.). So, a developer following behind another developer means following someone else's style, or re-writing in one's own style. It's not the end of the world, but it's an added challenge, and unnecessary grief if a native solution was possible.
Reading someone else's formulas is one thing. But it can be torture tracing through someone else's code, their error-handling methods, their way of writing loops, their non-existent comments, etc.
Still, I agree with you 100%: if native XL can't do the work, definitely go the VBA route. New VBA developers, need to just be careful, though. And I think that some VBA developers overuse VBA and make things unnecessarily hard for users.
1 week, 1 day ago on 6 Reasons Not To Use Excel VBA (Macros)
@Rick Grantham Thanks for commenting.
Rob's comment strikes a nerve with me, as well. For me, validation and user-interface aren't addressed enough. Those are things that I look for in "killer spreadsheets." Beyond functionality, are they easy to break?
What's the in-print book that you refer to?
I'm looking forward to the Dashboards book by @Option_Explicit. It should be out in a few months.
1 month, 1 week ago on Where are Excel & Google Spreadsheet Headed? Part 3 of 6
@ejazahmed1989 @OzData My man! Yes yes yes! You're talking my language. Sounds like you're pretty good with Excel, indeed.
I'm planning a blogpost where I show how to use INDIRECT so that you can pull data from whatever table you want, on the fly, with dependent dropdown lists.
Hey! I know who you are. I was at your blog earlier today.
Good material there. Keep going with it! It looks like you've just been at it since November 2013. I added you to my list of Badass Bloggers: http://datascopic.net/badass-bloggers/
1 month, 2 weeks ago on #2 of the Top 5 Excel Functions: VLOOKUP
@ejazahmed1989 what's your experience with Excel? Do you use INDEX/MATCH?
I do appreciate you dropping by and commenting.
@ejazahmed1989 OH LORD! I'm very aware of that conversation.
There are definite benefits for using INDEX/MATCH, and I've used it. This comes down to using the right tool at the right time. In a lot of instances VLOOKUP works. And especially for people who didn't even know that VLOOKUP existed, it explodes their minds when they realize they've spent years manually comparing lists and shaping data. VLOOKUP is a turning point.
INDEX/MATCH is a next level of expertise. It's 2 functions, one nested in the other, both with their own syntax. For beginners, I say, let's first learn VLOOKUP because it does work. It might mean rearranging some columns because VLOOKUP only works left-to-right, but we can do it. If we can't move the columns, it's time to jump into INDEX/MATCH.
@Mike Rudzinski Very bold statements, Mike.
I think you set up a very specific context to make your case. Some corporations may have effectively killed off spreadsheets. But every time one of these Excel-killers (e.g., SalesForce) is touted we have to have a conversation about For Whom, How and Why?
SalesForce is outstanding on the front-end. However, the back-end is pure hell to configure and manage. It's like comparing a helicopter to a Toyota Camry. The helicopter may be "better" as in "quicker." But who's gonna get licensed to fly it? Who's gonna maintain it? Where is it going to be parked? When there's an emergency to go somewhere, how long do we wait for the licensed pilot?
Is that anti-helicopter, anti-SalesForce or an apologia for spreadsheets? Hardly. It's a statement that not every entity is in a position to take advantage of something that we may agree would generate the best output and highest quality. It may cost too much in terms of just getting the darned thing off the ground.
Hey, if you have the wherewithal to ditch spreadsheets completely, more power to you. There are plenty of folks who've bragged to me that they manage data and haven't used a spreadsheet in 5 or 10 years. One person said, "When things get too complicated, I just program something in C++." I'm not mad at him. Good for him. But for every 1 of him, there's probably 50 people running a small to medium-sized business on spreadsheets.
Being a freelancer has put me in touch with business' concerns of:
1. Can we afford it?
2. Can we be without person X while they go train?
3. Can we risk trying something out and discover that it doesn't work for our needs?
4. Can we survive the upheaval of integrating something new?
5. Will people use the solution once it's been launched?
At $90 most businesses can get Excel working good enough and if someone needs support, they've got a friend, an aunt, 3 cousins, a co-worker, or an online forum for help.
1 month, 3 weeks ago on Where are Excel & Google Spreadsheet Headed? Part 2 of 6
@JordanGoldmeier Point taken, and I do pay $7/month for ProPlus. (And that's another frustration with Excel with their endless packages of products. In another blogpost I describe the week-long hassle it was to upgrade from Home Premiun to ProPlus.)
The free version of Tableau is on a public server. A Tableau rep was at a hackathon that I participated in and he told us that the public server is open for anyone to search and see your data. I saw Tableau and was blown away by it. I had a particular customer in mind. But, oh ... I don't think he'd like a public dashboard of his quotas, performance, commissions, customers' addresses, etc.
Tableau was great for the hackathon because the research and results were all public. Tableau is a great tool, generally. But I think this get us back to questions about For Whom? and For What?
Still you're 100% right to put Tableau into the mix because it's an app that takes a common spreadsheet use and does it better.
1 month, 3 weeks ago on Where are Excel & Google Spreadsheet Headed? Part 5 of 6
@xszil @Zack Barresse @OzData @amg49 ExcelWebApp was a huge addition. Mac users and users of ancient versions of Excel can have access to things like slicers and timelines ... as long as someone with Excel 2010 or 2013 does the initial development.
I'm loving ExcelWebApp.
The fact that you don't feel an urgency to load VBA on the web is a theme that's coming through to me in these interviews: our needs are so so so vastly different. After 15 years of working with Excel, I've rarely touched databases. My clients want apps and dashboards. Every time I go to teach myself databases, the phone rings and it's someone wanting a web-scraper or an inventory system.
It's fascinating and beautiful that our community has such a wide range of needs and skills.
1 month, 4 weeks ago on Where are Excel & Google Spreadsheet Headed? Part 2 of 6
@xszil @OzData @MrExcel Yeah! @MrExcel with a monster afro like Sugarfoot. That'd bring some "cool" to Excel.
2 months ago on Where are Excel & Google Spreadsheet Headed? Part 1 of 6
@davidhinckley It'd be good to hear what @MrExcel would say about that directly. I've done very little with Apps Script and gadgets but I can't speak with any kind of authority. What I did discover was some basic things that I could do in Excel very easily but would have to program in Google spreadsheet.
One example was cell validation where F5 cannot be greater than B5. Easy to do in Excel. At the time it wasn't part of native GS. So, I left it alone.
2 months ago on Where are Excel & Google Spreadsheet Headed? Part 2 of 6
@Zack Barresse @OzData @amg49
The on-the-fly updating is where I kinda understand the subscription model. It's a hassle but I liked waking up one morning and suddenly there were dropdown lists available in ExcelWebApp. One alternative was an HTML form hooked into a MySQL database via PHP. YUK!
So, it's nice to have the updates happen when they happen, as opposed to the major-release rigmarole that you describe.
@Zack Barresse Thanks for commenting.
I like how you describe Excel as an app. I detail that more in Part 3 http://datascopic.net/excel-and-google-spreadsheet-3/
But I wonder what "web spreadsheets" would look like. There are some that claim to be web-based Excel-killers but they're woefully weak and/or expensive.
ExcelWebApp is hugely promising in that regard. They've added the droplists and other validation features, and slicers are available, too. Do you think VBA will translate over, as @amg49 questions? Can it?
@amg49 Amy, thanks for commenting. This conversation has exploded in a lot of directions.
About 30 minutes from now part 3 will be posted and Rob Collie of @powerpivotpro mentions PowerPivot. So, you're right on.
@sgeren can you say more about the "socialization" of data? That sounds interesting.
@gregjkram Thanks Greg. One thing about Excel users is that we do have a community. All the contributors to the blogpost are way smarter than I am, maybe I can make up for it by facilitating more community. :-)
@MrExcel that'd definitely add some heat to your book covers. LOL!
@gregjkram thanks for dropping by and commenting.
@kdc care to clarify 'web analytics boo'? :-)
2 months, 1 week ago on Three Things I Learned About Digital Analytics From Teaching About Digital Analytics
Glad to meet you here again, @suzemuse!
My words for 2014: Vision, Choose! and Immodesty.
And here's the explanation: http://datascopic.net/2014-3-words/
2 months, 1 week ago on My Three Words for 2014
@sgeren thanks for commenting, fellow information evangelist. I didn't know you also teach martial arts.
Yes! 1:1 has had great results because we can get into nuances that don't make sense in large classes. That 30/30/30 rule isn't one I've heard of but it makes sense. Specialized classes tend to be smaller, and I wonder if the 30/30/30 still holds or gets any better.
3 months ago on 4 Insights From My Excel Students