2

I'm looking for a way to write code from a VB.NET add-in into a VBA project

So say write "Msgbox("Hello world!")" into a .bas module as part of an Excel project from .NET so the code written from .NET would be some pre written VBA stored as a string and injected into the VBA project

I have legacy code which I use for doing this sort of thing within VBA using the VBE properties to make a command bar for the VBA IDE and to write code into other modules or create new modules, but I'd like to be able to do this from either like I have said in the subject from VB.NET [preferably from a created add-in to be displayed on the Excel Ribbon] or maybe from a standalone winform app that can be minimised to the tray.

The two things I want to do this are for:

  1. To monitor VBA code performance, the legacy code I mention basically inserts a call to another sub at the start and end of each procedure in the project and writes out to a .csv so that you know how long each sub and function took to run
  2. To break up VBA project into component parts so that they can be added to TFS, basically loop through all the components of a VBA project and export them to a folder so they can be added to TFS enabling better source code tracking of changes

Can someone point me to examples [if any] of writing code to a VBA module from vb.net or give guidance on how this may be achieved.

I'm using .net framework 4.5 and visual studio 2012 with vb, but I'm fine with c# examples as well

Many thanks.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
spences10
  • 570
  • 1
  • 13
  • 32
  • 1
    Sorry, I misunderstood your question (comment deleted). I thought that you meant a kind of conversor VB.NET-VBA. Not sure about the VBE side of things, but regarding VBA (what says the first line of your question), you can communicate with Excel in various forms directly from VB.NET (as said, Office Interop of VB.NET is some kind of replica of VBA) and thus don't need to access VBA (although is possible, its reliability is not too clear to me). Can you please give a specific situation you have which you would like to address?, perhaps there is another alternative. – varocarbas Sep 12 '13 at 10:47
  • Yeah, what I'll do is add my specific examples what I'm trying to achieve into the question, thanks – spences10 Sep 12 '13 at 11:10
  • 1
    As said, you can run VBA code from VB.NET (here you have a link with some hints: http://stackoverflow.com/questions/4133094/run-vba-code-from-net-applications); although I always prefer/recommend to not mix platforms (either VBA or VB.NET communicating with Excel). Your second point seems to support my aforementioned suggestion: perhaps you should move everything to VB.NET as far as TFS is meant for VS (VB.NET) and there is no true compatibility VB.NET-VBA allowing to use this framework. Perhaps someone has any other idea... – varocarbas Sep 12 '13 at 11:25
  • I would love nothing more than to manage all of the workbooks and applications from Visual Studio alas there are some projects that refuse to be brought into the 21st century but we need to be able to track any changes made on the code base, so TFS is a good way to do it over comparing an old version with text compare or something. Thanks for the comment I will take a look at the link you posted. – spences10 Sep 12 '13 at 12:30
  • 1
    No problem. But I don't think that there is nothing you cannot port from VBA to VB.NET (same thing from VB.NET to VBA for any MS-Office-related stuff); logically, it takes time... but it sounds as something you will have to do anyway at some point. – varocarbas Sep 12 '13 at 12:51

1 Answers1

2

I assume you've figured this out already, but just in case:

The VBE object tree is exposed through automation - just get the application object for whatever Office file you're dealing with via a CreateObject call, then do the same thing you did in VBA (with appropriate changes from VB6 to .net, obviously).

exl = CreateObject("Excel.Sheet")
exl.Application.Workbooks.Open("whatever.xlsx")
'exl.Application.VBE.ActiveVBProject etc etc
DoraTrix
  • 127
  • 12