Some time ago I published a blog post about an add-in that I’m building, and the reasons why I elected to start using Add-In Express to manage the process. I still think Add-In Express is the best product for this kind of task, but figured I’d publish this post mainly to save me time the next time I get a new PC and have to go through this again.
Following the steps that Eugene provided set me off to the races… with Excel 2010. But when I tried to debug in Excel 2013 I wasn’t having much luck. I changed the “Program to Start” to Excel 2013, set a breakpoint in my code, and started debugging. Even though I’d set a breakpoint in my code, it was never activated. Instead, the breakpoint goes from a red dot to a red circle with a white fill, and mouse-ing over it yields the message “The breakpoint will not currently be hit. No symbols have been loaded for this document.”
This really sucks, as it’s pretty tough to work with. So what’s different, and how come Excel 2010 works, but Excel 2013 doesn’t?
The action of modifying that host config file as described by Eugene actually creates a new file that is stored within the appropriate Office subfolder held within the Program Files folder. Unfortunately, the program that is defined in your “Program to Start” settings seems to be ignored, and it actually creates the file in the folder that holds the oldest version of Office on your system. So in my case it created the following file:
Now, I stand to be corrected on this, but I believe that this file is only used when launching the debugging tools from Visual Studio. When the app is started it will look for this file and, if it finds it will use it to provides the debugging symbols back to Visual Studio for the .NET framework specified.
At any rate, the key here is to copy this file and paste it into the same folder that contains the exe file specified in the “Program to Start” area in the Project Properties of Visual Studio. And once you do that:
Beautiful! Works nicely.
It’s also worth mentioning that this setup works whether you are using a version of Office that comes from an MSI installer file (such as a DVD or volume license version of Excel/Office), or if you are using the Click to Run (C2R) version of Office that you can download from Office365. The only thing you need to be concerned about is the file path in which to store the config file and find the Excel.exe executable:
A while ago I started transitioning from VBA to VB.NET again, attempting to build a tool to manipulate the new Power Pivot components in Excel 2013. While I was able to get part way to a working solution using Visual Studio 2012 and VSTO, I ran into two key issues:
1. VSTO seems to be “bit” specific, meaning that I would need to keep one version of the solution for 32 bit versions of Office, and another for 64 bit versions, and
2. While I could run my code, the Power Pivot engine would crash on me unless I opened Power Pivot BEFORE I ran any of my code.
Both of these issues were rather severe to me, as I didn’t want to maintain multiple versions of the same code, nor could I release something and expect users to open Power Pivot before running my project. A friend of mine suggested I try Add-in Express to deal with these issues.
In the spirit of full disclosure, I contacted them and asked if they had a trial version. They don’t, but offered to let me trial it if I’d blog on my experiences. I agreed to do that, and what follows is my honest observations of the software.
I do want to preface that, like my friend Rob Collie, I am not a “Read the manual” kind of guy. Ironic, since I write a lot of material, but I take the approach of diving in, and hitting Bing for a quick pointer when I get stuck. It usually causes me a lot of pain, but I tend to learn better that way.
So, here’s how it all came together for me…
When you install Add-in Express, you get a new set of templates. I started my project by creating a new ADX COM Add-in targeting the .NET Framework 2.0. (For those not in the know, in order to target Excel 2010 with VSTO, you need to use .NET 4.0 and Excel 2013 is .NET 4.5. So .NET 2.0 is way too early a framework to have ever even heard of Power Pivot!)
From there you pretty much follow the prompts through the setup. I called it “Connect”, I set the minimum supported version to Office 2010 (since PowerPivot didn’t exist in 2007), I chose a Visual Basic Project, I selected Excel, and I left the rest of the settings at their defaults. Pretty easy, that part.
Once I had a project to work with, I created a new VB Module, just like normal, and built the code I would need to refresh my PowerPivot model and all the PivotTables. (Be aware that, for simplicity of the post, this is 2013 specific code, and will not work with Excel 2010.)
The key piece in this is making sure the xlApp declaration is correct, as you need to refer to the AddinModule portion in order to bind it to Add-in Express’s handlers, instead of just binding to the Interop.Excel objects. That change is what makes Add-in Express work:
MsgBox("Sorry, but I could not refresh the model! Are you sure this workbook has one?")
'Refresh all PivotTables
For Each ws In xlWorkbook.Worksheets
For Each pvt In ws.PivotTables
So far so good. Now I needed a user interface.
To be fair, it took me a bit to figure this one out. Once I finally realized that you need to open the “AddinModule.vb” portion in the solution explorer, then add a Ribbon tab to the canvas, then things got easier.
Despite reading very little documentation, with a little help from the blogs and articles on the Add-in Express site when I did get stuck, I was very quickly able to build a simple UI. There’s a good article on doing this here, which I wish I’d read earlier in the process.
As I say, it’s a really simple UI: a tab called “Model”, a single group, and a button with an image on it.
One criticism I do have is that it would be nice to be able to link the buttons and other controls to their callbacks inside the visual designer. As is, it’s a bit clunky, as you have to select the designer, then choose the other controls in the properties window. It’s not totally intuitive, but once you know where to look (read the article linked to above), it is workable.
My callback code for my button (which I didn’t bother renaming) is as follows:
[vb]Private Sub AdxRibbonButton1_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles AdxRibbonButton1.OnClick
And with that done, it came time to debug. Again, fairly straight forward:
Build -> Build the project
Build -> Register ADX project
Make sure Excel is closed
Start the debugging engine
My “Model” tab showed up, with the command I’d built, as shown below:
And with a single click (and a bit of a wait since Power Pivot is so slow), my Power Pivot data was refreshed, and the PivotTable updated to reflect the changes I made in my database. Notice the new customer and the new sales transactions for 8/1/2013:
But the best part is this:
The solution is deployable to both 32 bit and 64 bit Office platforms, and
I can open my Power Pivot project even if I run my code first, and it doesn’t crash.
I can honestly say that I fought that Power Pivot crash issue for about 2 months with VSTO, and I was really worried that it was going to kill my project completely. No amount of searching would turn up a fix, and other help calls didn’t yield any gold either; where they were answered, it was with a “don’t know” answer. Add-in Express has actually made this goal achievable.
I’ll also tell you that, while refreshing Power Pivot isn’t the focus on my full project, I have been able to use Add-in Express to successfully target and manipulate Power Pivot in both 32 and 64 bit versions of both Excel 2010 and 2013. I.e. multi version deployment with one code base. Pretty damn awesome.
I should also mention that their support has been phenomenal as well. Not only have they answered my emails, but I even ended up on a call with one of their lead people to examine why I didn’t seem able to use the debugging tools at first (a blog post for another day). 30 minutes, problem solved, and I’m good to go. Again, pretty damn awesome.
Readers of this blog will know that I don’t endorse very many products at all. Sure, I use Google Adwords and stuff, but I don’t write too many blog posts talking about how awesome a product is. Here’s my word on Add-in Express:
I’m sold. This product has been a life saver, and I won’t develop using VSTO.
At the last MVP summit I had discussions with a couple of people about something that has been burning away in the back of my mind for quite some time... creating my own Excel forums.
I've been absent from the online forums for the last few years, and I've greatly missed it. With the amount of teaching I've done over the past few years I've constantly been referring people to other forums and blogs, and I've received several comments from those people asking about a forum on my own site. So during this time I've been musing it over, wondering if there is enough differentiation, or enough reason to take on the task of building and maintaining another forum out on the interweb.
Despite missing a few of my good friends at the last summit, it was one of the most exhilarating ones I've been too, for a variety of reasons. Some of it was technology based, of course, but there was some based on conversations with people that I never expected to have. The compounding of all of it together made me realize that I have been putting this off for too long, and that I actually needed to do it, to bring back the piece of me that I've put off for my job over the past few years.
To this end, I've begun the process of building another forum on the web, one that focuses on Excel and Excel users. I am hoping to grow it with the style of friendly community that was fostered at VBAExpress.com, but have the focus strictly on Excel users from start to finish, no matter the platform. There are forums there for Excel Client focused areas, Programming (VBA, .NET, XML, etc), PowerPivot and data needs, the recently released Excel WebApp and even Sharepoint. As I said, I want this to be a one stop portal for anything Excel related.
In addition to the Excel focused stuff, I've also added a simple section on the other Office apps. The reason for this is simple; every Excel user encounters the other Office apps, and runs into issues. I think it's important that we have a place to ask/answer those too.
Oh, and naturally there is a chat forum, as no forum would be complete without it.
The forums are, of course, completely free to use. Simply sign up for an account, and post your questions. You'll be notified immediately by email when someone replies, so you can jump right back to the forum and post further information if required, or see the solution that an expert has helped you build. You can even upload your own files to the forum so that people can see EXACTLY what you're trying to do. After all, a picture saves 1000 words, right?
And if you're an expert, or even if you'd just like to help, then post some answers! We love help, and encourage you to take part. Speaking from my own experience, I can tell you that there is no better way to better your own skillset than by trying to help others. You won't always have perfect answers, but nor do we expect that. This is all about people helping people. The intentions are what is important.
As I mentioned in this post, I started working my way through the .NET Hello World example in PED 2nd Edition. I ran into a funny issue though, where it kept opening up my application in Excel 2003 instead of 2007. I fired off a quick email to Dennis who was kind enough to give me some pointers.
As it turns out, my "Current Version" of Excel on my laptop was set to 11 (Excel 2003). My attempts at running Office 2007's Detect and Repair feature, to reset this to 12, were causing the Microsoft Bootstrapper to crash. (So it wasn't completing.)
Today I decided to install Acronis TrueImage to take a backup before I mucked with this any further and, lo and behold, it pointed out that it couldn't install until it killed the suspended install of Office 2007. I gave it the assent to kill the job, and then my Detect and Repair ran fine. I'm now back up to Excel.12 as my current version, and my "Hello World" application works nicely.
I'm only about Â½ a chapter into PED 2nd edition, but the .NET stuff is working for me so far. I'm looking forward to digging a little deeper into this now, and trying my hand at a full managed COM Add-in.
After playing around in Visual Studio, and taking my first foray into the VB.NET world, I thought it might be kind of interesting to try and see just how portable .NET code really is. So I decided to port the little Math Game to my Windows Mobile 6 phone.
Guess what! I did it, and it wasn't actually all that difficult to do.
The conversion of the code was pretty simple. I just exported the userform from my original Windows project and imported it into a new Windows Mobile 6 application. (Err... after I downloaded the Windows 6 SDK which doesn't ship with VS2008 Pro.)
It worked just fine in the Emulator, so I then set about the deployment process.
It took way longer to get the deployment done than to write the project, I'll tell you that right now. You can't install an exe file on Windows Mobile 6, but rather have to package it up in a cab file. Great, but how do you do that?
After some searching, I finally found http://msdn.microsoft.com/en-us/library/zcebx8f8.aspx, which takes you right through the process. Fortunately I do have a digital signature at work, so I was able to use that to sign the code as well. I'm not sure if the certificate would be trusted on anyone else's device or not, but it did work on mine.
So once I deployed the cab file to my phone, the install was easy, and the app works. It just goes to show you just how portable simple .NET code can be, which is really cool.
Despite all the goodness here, there are a couple of quirks that I ran intoâ€¦
In the emulator, I can get my shortcut to show up in the Games folder off the start menu. For some reason I can't get that shortcut to show on the actual device though. Instead I have to dig down through the File ExplorerÃ Program FilesÃ Games. Ohâ€¦ waitâ€¦ I think I got it. I was setting up a shortcut in the Games folder, but it looks like it needs to be in the "Games Files" folder.
My icon file only shows up when the shortcut is selected. When it's not selected it looks like a DOS box.
I can't figure out how to turn on the NumLock when the game starts. (I'm sure it's easy when you know what you're doing.) This means that you have to hold down the FN key in order to get a number into the box.
On my phone it also enters the numbers in a pop-up box, which you have to confirm, before you can press enter. (Nevermindâ€¦ that's the XT9 Predictive Text feature.)
I wish I could stop the incessant beeping when you click enter and the messagebox pops up, but don't know how to do that.
At any rate, on the whole it works, and that's the good thing.
I have no idea if this works on Windows 6.1 or Windows 6.5, but it seems to work on Windows 6.0 for me. I'd be really curious to know if it installs for anyone else. If you'd like to give it a try, you can get it here.
I wrote this as an easy project to break into VB.NET. I'm actually feeling pretty good about it though, as it took me a couple of hours to code a working game, never really having done a lot in VB.NET before. (Most of that time was futzing around with the syntax.)
Based on my tests it even installs in Windows XP and Windows Vista without a hitch too. I was pretty surprised, as I've heard horror stories about deployment, but I think those might have been related more to VSTO.
At any rate, here's a picture of just how complex this is:
It generates a random equation which can be addition or subtraction. And it tracks the player's stats for the current session only. It was designed for a 6 year old to play, and so far the testing has been positiveâ€¦ although it needs some kind of excitement factor as after about 20 questions it loses its luster.
It does require the .NET 3.5 SP1 framework to be installed. That takes forever compared to the install of this little thing.
At any rate, if you want to try it out, you can download it here:Â mathgame.zip.
On Friday night, I began reading my way through PED 2nd Edition. After something weird happened with Excel, (I think because I have Excel 2003 and 2007 installed,) I got the bright idea to create a very simple Windows Forms application. I figured that I could make a really easy math game for my daughter. (I'll see about posting it over the next few days.)
So cool thing hereâ€¦ with very minimal VB.NET experience, I was able to knock up a fully working simple math game in about 3 hours that will install on Windows XP and Windows Vista. I was pretty damn impressed.
At any rate, here I am today, back working in Excel. And I can now say that I'm rather disgruntled with the state of the IDE for Excel VBA.
Despite gnashing my teeth on certain stuff in VB.NET, the IDE in VS2008 is awesome. Maybe it's for the big things, and I'm sure that there will be VBA IDE things I'll miss, but it's the little things that made my day. Little things like this:
You can collapse procedures/function and even entire regions of code. That seriously rocks!
You type in With "whatever", and it automatically puts in the End With. Actually, all constructs are like thisâ€¦ For Each, Select Case, Try/Catch and so on. Very, very cool!
The indenting everywhere is automatic.
The intellisense always suggests available items. I find this somewhat hit and miss in Excel's IDE for some reason.
I really wish that they add the features above into our tired old IDE. It would make things so much faster for typing up code from scratch!
The last version of the book was excellent: the only book on the market of its type, in fact. I'm expecting nothing less from this one, indeed I'm actually expecting more. Dennis Wallentin (XL-Dennis) was added to the team this time to bring in the .NET aspect, and I'm really looking forward to exploring those chapters. Hopefully I'll actually be able to make a .NET add-in that actually works now!
I haven't had a chance to dig into it yet, but one thing I'll say about the book right now is that it should be able to hold your house down if a tornado hits it. This is one big book!
Over the weekend I've spent a significant amount of time working in Visual Studio 2005 with VB.net. The more I work with it, the more I like the IDE, and some of the cool things that I can do in it. This post, though, is going to focus on some of the differences between VBA and .Net that I'm playing with.
First thing to notice in importance is the existence of Option Strict. As VBA'ers, we're used to using Option Explicit (at least you should be), but Option Strict adds another dimension to your code. As I believe in following best practices, I am coding with both Option Explicit (OE) and Option Strict (OS) set to On.
It should also be noted that while I am quite proficient with VBA, I am certainly not an expert on VB.Net, and I am not trying to pass myself off as such. It's very likely that I will get some things wrong in this, and if I do, feel free to comment on the post. I'm doing this to learn, and blogging to share my experiences with those who have both gone before, and may choose to come after me on this road.
Okay, so here we go...
Early vs Late Binding
In VBA, we have the option of Early or Late binding. Generally, I develop using late binding, mainly because I work in an environment of mixed Office versions. There is nothing worse than having an Excel 2003 user open a file, have the references upgraded, and the 97 user can no longer get in. In VB.Net, with OS on, you must use Early binding.
In addition, OS demands very strict Type casting. I'm used to the following to create an instance of Word in VBA:
In VBA, we'd use our Right() function to return x number of characters from the right of a string. In VB.Net, we need to preface that with something more:
Probably the biggest issue that I've run into is casting types. VB.Net (OS on) will NOT convert data from one type to another. This is evident in the binding example above, as well as many other things. Fortunately Option Strict kicks up little error messages to tell you what's up, and after a bit you can generally make some sense of how to fix them. Usually it involves a converting something to a type that we're used to... CStr, CBool, etc... What really threw me, though, was how to type data as an Object. The secret is the Imports statement... For example, look at the MessageBox constants (more on this below).
With a msgbox, you can't just use the constants like vbYesno anymore. You need to use Microsoft.Office.Core.MsgBoxStyle.YesNo. Now that's a bit of a pain, but there is an easier way. Thanks to Mike Rosenblum, who explained this to me in great detail, you can do this:
At the top of your module, insert the following statement:
Now you can refer to that constant as:
In addition to the issue with Type Casting above, the messagebox also changed a bit in that you don't add your options together in .Net as we did in VBA. Let's look at the differences:
Select Case MsgBox("Are you sure you want to do this?", _
vbYesNo + vbQuestion, "Are you sure?")
Case Is = vbYes
Confirm = True
Confirm = False
Select Case MsgBox("Are you sure you want to do this?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question, "Are you sure?")
Case Is = vbYes
Confirm = True
Confirm = False
End Select [/vb]
Notice that it is an Or statement, not a +. This seems weird, but I have tested it, and it does work. Seems counter intuitive, I'll grant, but what are you going to do.
Well, that's my observations so far on language differences. There's way more, obviously, and I'm sure I'll share more as time goes along. 🙂