PDA

View Full Version : Solved: VS2012 and VBA



TrippyTom
12-18-2012, 03:54 AM
Hi gang,

I simply want to convert my Office 2003 macros (from custom toolbars I made in Excel or PPT) to work in Office 2007. After going in circles during my research I finally broke down and purchased Visual Studio 2012. Unfortunately, I still can't figure out how to get it to recognize VBA code.

Does anyone here have experience with this that could maybe explain it to me? I'd rather not have to do it the "hard" way since I already have VS2012.

For instance, I used the visual designer to setup my buttons on a custom ribbon, but once I tried to add my code for the buttons, the project wouldn't compile because it doesn't like the VBA code. I'm guessing I'm probably missing some "includes" or something for it to recognize VBA, but I'm fuzzy on that part.

Any pointers to links would be appreciated too.

Thanks,
Tom

Bob Phillips
12-18-2012, 05:53 AM
Visual Studio doesn't do VBA, VB.Net, C# and so on, but not VBA.

Why do you think you need Visual Studio, VBA works fine in Excelo 2007.

BTW, you would have spent your money more wisely buying Excel 2010.

TrippyTom
12-18-2012, 09:10 AM
I bought VS2012 because I wanted to use it for more than just Office development. As a sort of investment in my future learning. I got it to make my life easier (or so I thought).

There seems to be a huge gap of documentation between power users of Office and medium to advanced programmers. All the documentation I've read so far is about how to make your own apps for Office. I don't want to make a complete app and sell it on the Office Store. I just want to know how to use the proper Interop types to use and so forth (but as this is all new to me I could be completely misunderstanding it all) so it will recognize my VBA. Or if that's not an option, I need to figure out how to recode my addin with C# or VB so I can manipulate cells in an Excel sheet.

This link suggests it can be done, but not easily.
http://stackoverflow.com/questions/11604212/can-i-use-vs-2012-to-create-an-office-2007-add-in

My workplace is still using Office 2007, so Office 2010 is not an option for me yet. They say we're switching in a few months, but I'll believe that when I see it. Everything happens at a snail's pace when it comes to software upgrades.

Bob Phillips
12-18-2012, 12:25 PM
We're conflating two separate things here.

If you want to create addins that manipulates Excel, you can do it the same way that we have been doing for years, using VBA. You might want to include some ribbon controls in your addin which of course is new, but other than that nothing changes.

If you want to learn the new stuff, C#, VB.Net, (although it is debatable how much of a future this technology has in Office now, it's all webapps, JavaScript, and HTML), then go with it, maybe using one of your addins as a learning exercise. But don't underestimate the learning curve though.

And, as you have found out, VS 2012 only has Office 2010 templates, so you need to know what you are doing when programming to Office 2007, which is a challenge when you are still learning.

TrippyTom
12-18-2012, 03:51 PM
So we get back to the point of my original post:

You might want to include some ribbon controls in your addin which of course is new,

I made buttons in the visual ribbon designer, but I don't know how to link that to my vba code. What's the easiest way to do this since I can't use VS2012? I'd rather not have to resort to editing everything in notepad, but if that's what I have to do I guess I'll have to deal with it.

TrippyTom
12-18-2012, 04:22 PM
Actually, I found this and think it might be a step in the right direction. As usual, my original research led me down the wrong path. :(

http://www.youtube.com/watch?v=di-EFzJpnn8

I'm going to mark this solved so I don't waste anyone else's time.

Bob Phillips
12-18-2012, 04:36 PM
Adding to the ribbon is simple, you don't need Visual Studio to do that, there are tons of online examples.

HYou need to get hold of the CustomUI editor though, a bit notepaddy, but better than nothing http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/07/7293.aspx

TrippyTom
12-18-2012, 05:33 PM
Thanks James,

With that program, I was able to figure out I could:
- export the ribbon design part out to xml from VS2012
- then I copy/pasted the xml into the custom UI Editor (I only had to tweak it slightly) and it worked.

This saved me a TON of time setting up all those buttons again!

TrippyTom
12-18-2012, 05:38 PM
Is there a way to make the custom tab application-based, instead of file based? The tab is only visible when I switch to the file that I put the xml code into.

TrippyTom
12-18-2012, 05:42 PM
oh nevermind, i figured it out :)
put it to an Excel Addin, not macro-enabled workbook. duh.
I think I need to go to bed.

Bob Phillips
12-19-2012, 01:07 PM
oh nevermind, i figured it out :)
put it to an Excel Addin, not macro-enabled workbook. duh.

Exactly!