Well, this is exciting because I have for years always wanted to use Visual Studio to build my apps in Excel. I am using Office 2010 (my client uses 2010) I am beyond a VBA junkie and there is little or nothing I can’t do. However being able to add to a .net class etc. has always been missing. Also I wanted to see how hard it is or not to develop in VS. Using the VBA editor and being able to debug on the fly saves SOOOO much time. But long macros TAKE SOOOOO much time to. Can things be sped up? Is it that hard to edit/debug? I never knew – now I will. The template for VS 2015 Office apps is meant for Office 2013+ but I got it to work for 2010 – see the humorous adventure below.

Here are some tips along the way ( I have had some snags already – hence this post)…Installation – first, in VS 2012/2013 you would click a button upon install  to include the VSTO tools for Office you could never add it after the fact except for a re-install. These guys pointed to a link that was the key for VS 2015 (and this link). Below are the relevant bits (I usually don’t do this but I had to wade through that to get links that said visualstudio.com or microsoft.com – I don’t trust the other links because I don’t know the source or downloads from them.).

Here is the software that you run AFTER installing VS 2015.

Here is a Microsoft article “my first Application-Level Add in For Excel“. Hey! There – I can type code! Lets go… READ THE NEXT STEPS! Good stuff and inspiration of what you can do with an add-in, ribbons etc. and deployment. Normally I make a hidden read-only excel .xlsm file on the network that a button would point to so many people can use it at once while I could improve or make new additions at the same time. These are works-in-progress in an engineering department – lots of changes and improvements on the fly for a small group of us.

Lets do a hello world popup just before we save. This is like the demo but a little different.

But alas – no luck yet (UPDATE: I gave up and went to edit some more VBA and when I clicked “Save” – THE CODE BELOW IS WORKING!!!!!!! So it works, it jsut cant start Excel. I have a couple other articles – keep reading past “I give up for now”). It complains that I don’t have the correct version of Excel installed.  After some more digging and clicking the Help button from Visual Studio (hey – ONE help button worked from a Microsoft app and was useful!) it suggested I install Visual Studio 2010 Tools for Office Runtime (click for download link). I had to quick SQL and IE (good thing I am writing this in Chrome). I give up for now…. I got it working in VBA as expected (what can’t you do in VBA 🙂 )

NO NO – Stop! I don’t give up after I gave up and commenced VBA programming it showed “Hello World” in a popup and in the lower left. … Do these steps before you click “run”

1. Click “Project->Project Properties…”
2. Click the debug tab
3. click on the radio button “Start external program”
   1. type in C:\Program Files\Microsoft Office\Office14\EXCEL.EXE (remember I am using Office 2010 64 bit)
4. You MIGHT need to change your Reference to point to Excel 2010 instead of 2013. I did – and it worked but I don’t know if you have to (leave a comment if you don’t have to do this)
   1. in VS, right CLick your References (in your Solution Browser Tab)
   2. Click Add Reference
   3. in the Assemblies > Extensions remove the checkmark besideMicrosoft.Office.Interop.Excel       15.0.0.0

      and choose (click to left)

      Microsoft.Office.Interop.Excel      14.0.0.0

   4. Click OK
5. Click run or the play button
   (here is the blog article from 2008 that gave me the hint to try this 8 years later … wow 2008 and it applies!)

Then delete or “Clean Code” as this app is REALLY annoying when you are using excel (this sentence was added much later than this article was written)

Here is my code.

[c language=”#”]
// add this at the top
using System.Windows.Forms;

// add this into the ThisAddIn_Startup method

//from the sample website
this.Application.WorkbookBeforeSave += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeSaveEventHandler(Application_WorkbookBeforeSave);

if (this.Application.Version == "12.0")
{
// 2010-specific code.
}
else
{
// 2013/2016-specific code.
}

//add this as the last method (a new one)
void Application_WorkbookBeforeSave(Microsoft.Office.Interop.Excel.Workbook Wb, bool SaveAsUI, ref bool Cancel)
{
Wb.Application.StatusBar = "Hello World from the status bar";
DialogResult msg = MessageBox.Show("Hello World", "Just For Fun", MessageBoxButtons.OKCancel, MessageBoxIcon.Stop);
if (msg == DialogResult.Cancel)
{
Cancel = true;
}
}
[/c]

Continuing on …

http://stackoverflow.com/questions/6923095/how-to-create-a-button-to-call-my-vsto-add-in

http://www.mrexcel.com/forum/excel-questions/639478-how-can-i-call-vsto-vbulletin-net-functions-visual-basic-applications.html

I think VSTO adds functionalities to your workbook and worksheets – so VSTO would add the buttons to the sheet and/or toolbar etc.