VBA - Automated Excel 2007 Document in Visual Studio

Question

I have currently written a sub, written in VBA, that is called by the click of a button on an excel document. The sub takes data in the cells of the document and sends them to a web service.

All of this works flawlessly when Visual Studio automates Excel.

I am wondering if there is a way to save this excel file to where it does not need Visual Studio to run. I want to be able to distribute the excel file to other people so they can use the web service with there own data.

Is this possible... If so how?

Thanks Alot!

Answer 1

Excel has a Visual Basic IDE built into it. You can press Alt-F11 to get to it or go to Tools -> Macro -> Visual Basic Editor. You can edit/add/write code here that will travel with the sheet you're working on.

To accomplish what you're looking to do, create a button in the spreadsheet. You can use the Visual Basic toolbox, which is accessed by View -> Toolbars -> Visual Basic. Create the button. If you double click that button while in design mode, the VB editor will be launched and it will take you to the code for that button. Paste the code you wrote into there and you'll be set. Your sub should look something like this:

Private Sub CommandButton1_Click()
    ' Code goes here
End Sub