none
How I open Excel add-in in Visual Studio 2010?

    Question

  • I have an add-in that need to add more functionality in it. I decided to use Visual Studio instead of using the VB editor in Excel. When opening the add-in in visual studio, I don’t get as a source code as it is when it is opened by Vb in Excel, and even adding it as a reference, VS does not recognize it. I am a beginner not professional.

    Thanks

     

    Thursday, April 07, 2011 2:14 AM

Answers

  • Hi zayouna

    <<Thanks, and sorry for make it long>>

    Long isn't a problem, if it helps understanding :-)

    So, if I compare the two screen shots, it seems to me that you're trying to put the UserForm (old VBA interface) into the Ribbon? And essentially, you want to execute the VBA code behind the UserForm from a Ribbon button?

    If that's the case, the first thing you'll need to do is analyse how the VBA solution is constructed. By that I mean, where all the parts of the code are. VBA programmers follow one of two basic "models": all the code is in the Code-behind-the-form, or only "stubs" are in the code-behind-the-form but the "real code" is in a plain module (or a class module).

    If the VBA solution was constructed with all the code in the code-behind-the-form, then you're going to need to do some re-design. You'll need to put all that code in regular modules, so that the procedures will be visible to the Application.Run method. This will probably involve more than just copy/paste as you'll also need to allow for values coming from the UserForm controls. In these cases, you'll need to add parameters to pass in the values from your Ribbon interface.

    I get the impression from your problem description that many things in the VBA code are repeated. If that's the case, then this would also be the opportunity to consolidate such procedures, or break out repeated code into separate procedures that can be re-used.


    Cindy Meister, VSTO/Word MVP
    Friday, April 08, 2011 10:08 AM
    Moderator

All replies

  • Hi Zayouna

    If you can see the code for this add-in in Excel's VBA editor, then it can only be edited in that environment. VBA code cannot be edited using any version of Visual Studio. VBA code is not VB.NET, it's a subset of the old, classic Visual Basic (COM).


    Cindy Meister, VSTO/Word MVP
    Thursday, April 07, 2011 6:58 AM
    Moderator
  • Thanks so much Cindy, this is helpful.

    Please may I get more info from you?

    What if I create a new add in VS, just to control the old add in Excel, e.g. activate forms and fill fields, click buttons, and so on the old Add-in? (As I understand from your answer, I will also not be able to access the namespaces for the objects in the old add-in)  Any idea?

    Thanks again for answering me, I might asked silly question as I am a beginner.

    Thursday, April 07, 2011 10:51 AM
  • Hi zayouna

    It's not clear to me from your second question exactly what it is you want to do with the .NET Add-in. You can certainly create a .NET add-in, and it can provide buttons that call VB.NET code that in turn calls existing VBA macros stored in Excel workbooks (Application.Run method of the Excel.Application object).

    Is that what you have in mind? If not, could you please be more specific?


    Cindy Meister, VSTO/Word MVP
    Thursday, April 07, 2011 12:34 PM
    Moderator
  • Thank you for replying; sorry for not being clear, here is it in detail:

    The Old Add-in has forms, buttons, multi selection Combo Boxes, and fields (to be filled by numbers), (no micros) and it is written in VBA. The problem in it: It is too manual; it needs a lot of clicks and numbers to be filled to draw the right numbers and types of charts. For example to draw 40 charts of 40 set of data, I need to redo all same procedures for each chart, where procedures are almost the same for all the charts.

    I tried to record macros to record my selections process, but it looks that micros cannot captures activities but from the Excel Cells (e.g. cannot record ribbon buttons clicks). I tried to use “send key” command of “Alt X” to activate ribbons buttons; it failed as Excel might assign different letter each time opening Excel when pressing “Alt” key for the old add-in ribbon’s buttons.

    In your opinion, what is the best way you would use to control the old add-in objects from the new add-in?  (simple hints which I can start from)

    Please have a look at these two pics, to give clearer idea:

    Old add-in pic (deleted some info as it is public)

    https://picasaweb.google.com/snzayouna/UntitledAlbum#5593056379887769090

     

    The new add-in under development:

    https://picasaweb.google.com/snzayouna/UntitledAlbum#5593056277236146162

     

    Thanks, and sorry for make it long

    Friday, April 08, 2011 4:04 AM
  • Hi zayouna

    <<Thanks, and sorry for make it long>>

    Long isn't a problem, if it helps understanding :-)

    So, if I compare the two screen shots, it seems to me that you're trying to put the UserForm (old VBA interface) into the Ribbon? And essentially, you want to execute the VBA code behind the UserForm from a Ribbon button?

    If that's the case, the first thing you'll need to do is analyse how the VBA solution is constructed. By that I mean, where all the parts of the code are. VBA programmers follow one of two basic "models": all the code is in the Code-behind-the-form, or only "stubs" are in the code-behind-the-form but the "real code" is in a plain module (or a class module).

    If the VBA solution was constructed with all the code in the code-behind-the-form, then you're going to need to do some re-design. You'll need to put all that code in regular modules, so that the procedures will be visible to the Application.Run method. This will probably involve more than just copy/paste as you'll also need to allow for values coming from the UserForm controls. In these cases, you'll need to add parameters to pass in the values from your Ribbon interface.

    I get the impression from your problem description that many things in the VBA code are repeated. If that's the case, then this would also be the opportunity to consolidate such procedures, or break out repeated code into separate procedures that can be re-used.


    Cindy Meister, VSTO/Word MVP
    Friday, April 08, 2011 10:08 AM
    Moderator
  • Thanks so much, this was helpful.
    Sunday, April 10, 2011 12:46 AM