I’m pleased to announce a new feature here on Methods In Excel – Podcasts.
In the first podcast, I caught up with Simon Murphy of Codematic and Smurf on Spreadsheets for a chat about professional spread sheet development, it was great to catch up with Simon and probe him for his thoughts! Enjoy.
Listen here, or download from the link.
Please leave some comments with any feedback about this podcast, and also any ideas for future podcasts.
Podcast: Play in new window | Download
When we talk about which language to use we often, if not always get talking about the technical advantages of a given tool, XLL’s are faster than VBA, VBA is easier to deploy than VSTO, VSTO is more secure than VBA, and so on and so on. Simon has some information here and I have some .Net stuff here. But one aspect that’s over looked is what each offers the developer.
Let’s consider an example. I’m writing a adding for the users in my department, it needs to contain some UDF’s, apply a custom formatting option to a selected range, help import a strangely formatted XML file, and link to a database and pull back some data. What are you going to write that in?
Well from a technical point of view you might like to used an XLL for the UDF’s, a Com adding for the formatting and database stuff and make use of some .Net classes for the XML processing. Nice!
For 90 percent of us, we will solve every problem using VBA, simply because that’s the only tool we have. But what if?
Here’s what I think different technologies offer the developer, not the technological advantage, it the things that make the developer’s live more enjoyable!
VBA: For simple stuff it’s quickest. As things get more complex and or the difficulty of what you are trying to do gets harder VBA becomes more of an effort (think of userform stuff, VBA, vs .Net?!). If you need something that needs constant changes, or something that you expect to grow in size over a period of time it might be worth doing it VBA.
VB6 (Com addins): If the UI you need is a bit more complex, than it might be time to switch to VB6, the more powerful forms and use of active X components means that you can have more choice when designing them. Also from a deployment POV, you have to worry less about the thing being installed on the PC, as you can compile them into the DLL
As the size and complexity of the project increases, switching to VB6 can be a help. Functionality can be complied in to different parts, external libraries can be used and packaged easily, source code can be shared with other developers – although this is rare for Excel projects.
Another good reason for a developer to pick VB6 com adding over VBA ones is that, although generally VB6 code can be changed with little effort to run in VBA, there are times when VBA does not quite have the same feature set, and dropping some code straight in to a VB6 com addin is just easier than messing around trying to convert it to VBA.
VSTO: Well its IDE isn’t 100 years old, thats a big plus! But the most compelling reason why an Excel dev would want to use VSTO, would be to make use of the framework. Forms are incredibly powerful in .Net (form namespace), but so too is all the functionality that the framework provided, like the threading name space, or the services name space. When we start to try and do really grand stuff, .Net is brilliant.
C/C++ [XLL]: Purely from an easy of development POV, I can only really think of 2 things XLL would over. One would be a situation where you need to deploy is a “hostile” environment, so the extra work in the coding would be repaid in the effort saved deploying. The other would be if there is a C library with some complex functions in it, a bit like with the VB/VBA issue, it might just be easier to keep the C code and wrap it up in a XLL.
VSTA: What’s VSTA?!?!
So there you go that’s my take on it, disagree?
Well my copy arrived this morning, and what a beauty it is! I’ve obviously only had a cursory glance (stupidly busy with work – UAT any one?) but I have to say that it’s really impressive.
There’s loads of updated content from the first edition and it’s an extra 200 pages long. There’s an interesting chapter about interfaces that looks really good, the .Net stuff looks excellent and there’s lots of Excel 2007 info in there, including a chapter about cross version applications. Not to mention all the samples and code on the CD!
If you develop in Excel, do yourself a favour and get a copy, go on, you deserve it! Looking on Amazon, the second edition appears to be cheaper than the first edition, which is fun! You could always try an win a copy over at Dicks!
Oh, and dont forget the PED support forum!
Back in 2005 Stephen Bullen, Rob Bovey and John Green collaborated on one of the most useful Excel development books ever written. That book, affectionately known as PED (Professional Excel Development) has been a staple for most Excel Devs ever since. As you can see my copy has had a lot of use!
I am happy to say that the second edition is due out later this year, and XL Dennis (aka, Dennis Wallentin) has joined the team to share some of his .NET expertises. I caught up with Dennis to delve into what we can expect from the refresh, his views on Office and .Net and the sate of public telephone boxes in Sweden!
Hi Dennis,
Q: I notice that 2009 marks the 20 year milestone for “XL-Dennis”, for the few readers that might not be familiar with your work and background could you tell us a little bit about what you do?
A: I have a Masters in Business Management & Accounting and I started to use spreadsheet in the mid 80′s. Back then Lotus 1-2-3 was the #1 spreadsheet tool which I used on a daily basis. The concept of XL-Dennis was created in the end of the 80′s and it started out with the Excel 2.1c version. XL-Dennis utilises Excel and other tools to develop professional business solutions. For the last 3-4 years I have been specialising in .NET and VSTO and today most clients’ solutions are based on these two technologies.
20 years with Excel has so far been an exciting journey and I’m looking forward for the coming 20 years.Q: How did you become involved in the second edition of the PED?
A: When Rob and Stephen outlined the second edition they wanted a new section that covered Excel and .NET. They quickly found out that there are not so many developers in the Excel community that have the knowledge and the practical experience to work with Excel and .NET. So they ended up with a non-English and non-MVP person like me.
Q: I believe this is your first outing as an author, how does it feel to have your name in print and how much work did it all take?
A: That’s correct although I have contributed with some materials in other Excel books. Having your name associated with these guys’ is an absolute honour. To see your name on the coverage (second billing) on a book like PED is very pleasant and satisfying given all the work.
I didn’t count the hours but imagine that you start out with a blank Word document and the final version is 50-60 Word pages per chapter. Although my English has improved the language itself is a barrier that has consumed much time for me.
I take this opportunity to thank Rob for all his support. He has been the team leader for this edition and in my opinion he has done an excellent job. I would also like to thank Gabhan Barry (a Program Manager in the Excel group at Microsoft) who has been the technical reviewer of the .NET chapters.Q: That’s a significant effort, was it worthwhile, would you do it again?
A: I would say it’s nice when the work is done but during the process you go through all the emotions. At present I would turn down any offers however in the future, and if a new edition of PED is to be produced, then I’ll have probably forgotten all the hard work and say “yes” again.
Q: PED was a critical success, how did you go about writing new material for it. Was this a challenging undertaking and did you feel you had to live up to the high standard it originally set?
A: Initially it was a burden for me but when I realized that I’m the guy with all the .NET knowledge and experience in the team I managed to place myself in a better position. Because I always try to deliver high quality myself the standard of the first edition was not an issue for me. In fact, I got more motivated.
Q: PED’s treatment of Excel was as a powerful development platform. It was aimed at experienced developers, really wanting to push the boundaries of Excel development. Should we anticipate the seconded edition to be pitched at the same audience?
A: Yes, the second edition also targets the professional Excel developer. We continue to support best practice and try to catch up with the latest technologies .NET and VSTO. This is the first Excel book that covers VB.NET and VSTO in detail.
Q: What can we expect to get out of the second edition, what’s new and what’s been updated?
A: Because this is the second edition we feel it is important to be very clear on this aspect so that potential buyers can make a good decision to buy or not.
The second edition includes five new chapters. One chapter covers Ribbon UI in Excel 2007, another new chapter is about creating cross-versions applications and three chapters are about .NET and VSTO. The book has 29 chapters in total so 24 chapters have had minor updates.
If the potential buyer has little interest in .NET and VSTO and already has the first edition of the book then I would recommend to save the money and wait for the next edition.
It is also important to know that to follow the chapters about .NET and VSTO it requires VS 2008 Professional or later. To fully leverage the VSTO chapter it also requires Office 2007. In addition, to put .NET solutions in production it is also recommended using a commercial digital certificate.
With this edition we will launch a site devoted to the book. It will consist of updates, additional materials etc and a forum in which the readers can communicate with us.Q: The VBA community have been a little reluctant in adopting .NET technologies; do you see this as a problem?
A: As long as the standpoints are based on genuine business terms I cannot see anything wrong with it. For the professional Excel developer the question is not if rather when to adopt .NET. We hope that the second edition will get them started.
Q: VSTA shipped with info path in office 2007 but was not integrated in any other apps. Do you think getting VSTA in Excel would spark some passion for .NET with Office Devs?
A: Looking ahead we know that Microsoft will incorporate a new IDE with Excel. The question is two folded; in which version of Excel will the new IDE be available and which .NET language will be in use. To answer the question I must honestly admit that I have no idea.
Q: What are some of the major benefits to leveraging the .NET Framework over more traditional VBA solutions?
A: Two words: Security and deployment. With .NET we have a new security model and VBA has nothing like it. As for deployment we can now deploy our solutions in several (secured) ways.
Q: Like any new technology .NET has a learning curve. What do you think the average time investment would be for a VBA developer coming to .NET?
A: All I can say is that requires a lot of hard work. As a kick start I would recommend to take a class in general VB.NET programming.
Q: How do you see the future 5 to 10 years shaping up for Office developers? Will VBA still be king, or is a move to .NET just a matter of time?
A: In the foreseeable future we will work with two types of environment: the desktop and the web office. As long as VBA is around it will continue to play a central role for the desktop environment together with .NET while for the web Office environment .NET is the only available alternative.
Q: In the first edition chapter 22 was dedicated to Excel and .NET and its focus was VSTO. Will the update include details on managed Com addins and/or automation?
A: I’m pleased to say that the second edition includes three new chapters devoted to Excel & .NET:
Chapter 24 Excel and VB.NET (77 pp): This is an introduction to the VS IDE, VB.NET and automation of Excel with VB.NET. We have intentionally tried to pitch it at a beginners level.
Chapter 25 Writing Managed COM Add-ins with VB.NET (90 pp): This is the “flagship” for the .NET section in the book and we step through all the steps to create and deploy managed COM add-ins, including a discussion about managed Automation add-ins.
Chapter 26 Developing Excel Solutions with Visual Studio Tools for Office System (58 pp): In this chapter we discuss what VSTO is and when to use VSTO. In our experience this is a must so that the reader can decide if VSTO is something to pick up or not. We also discuss VSTO add-ins and single workbook’s solution together with how to deploy VSTO solutions from a web site.
For the chapter 24 and 25 we have a practical case study: PETRAS Report Tool .NET which interacts with a SQL Server database and with some predefined native Excel templates.Q: One of the often bemoaned issues with VSTO is that most of the sample code is presented in C#. Happily PED used VB, can we expect this to continue?
A: Yes, PED will still be supporting VB for the foreseeable future. It’s remarkable that Microsoft has such a strong focus on C#, especially if they want professional VBA developers to port themselves to the .NET platform.
Q: Following on from the previous question, what language type do you prefer and why, would you strongly recommend one over the other when targeting Office?
A: In general I would like to avoid discussions about which language to use and so forth.
Today we can meet clients’ requirements for Office solutions with a toolbox that includes a various number of tools, VBA, classic VB, VB.NET and VSTO. In some situations the choice is dictated by the customers or by their desktop environment. In other cases we can select tool(s) to be used on our own.
But if we only discuss the question from a strictly technically perspective then .NET is in favour as it offers a modern IDE.Q: When can we expect PED to hit the shelves?
A: The book is scheduled to be available in May 2009. (See it at Safari here)
Q: And finally, do you have any plans for a second international VSTO conference in the Phone Box, and will the beer still be free!!!!
A: Actually we will have the release party for the second edition of PED in the Phone Box at XL-Dennis Street. And believe it or not the second VSTO conference will also be held there and at the same time! In addition to the free beer we will also give away unknown bugs in Excel for free too!
Thanks Dennis, I’m sure the second edition will be a success!
For those too impatience or too excited to wait for PED2, why not hop over to Dennis’s blog in the mean time, it’s loaded with Excel and .NET posts covering a multitude to subjects.
Sam asked if you could build custom task panes with VB.Net Express edition. The simple answer is no. However this question raises a deeper issue.
What are all the options when it comes to extending Excel (or any other office app for that matter). Most of us know about XLA, XLL, DLL, etc but the .Nety stuff is not as well understood. In an attempt to shed some light on all of the options I have tried to make a simple table weighing up the options I’ve also bugged VB6 and Delphi in at the end just to help compare the options.
I should point out that I haven’t even used some of these technologies myself, so I’m not suggesting this is the definitive answer, but I think it’s a fair reflection, and I can tell you that I’ve not be able to find anything else like this on the net, so this is a World Exclusive!!!
Heres the PDF wall chart!!!!