Solved: Advice, please - VBA vs. Visual Basic.Net

[JWhite]

I have an Excel add-in application for corporate finance groups which started out fairly simple but has gradually morphed into a fairly compex application. I'm an experienced programmer but NOT in VBA or anything like that until this project. Our small company's expertise is more in the server side of large ERP databases but we've discovered ways to use VBA with Excel 2007 to make our data much more useable.

Now we have several companies interested in the product and the prospect of a wider market. We're already running into companies that don't allow VBA on desktops due to security concerns and I've run into my own problems with installing VBA add-ins in a corporate network environment that are concerning me.

We've talked with some fairly experienced people who say it's time to make the move to Visual Basic.Net or C# if we're really planning to sell this to a large audience - which we are. I'm groaning at the idea of converting 3,000+ lines of VBA code plus 10 Userforms which, apparently, aren't supported AT ALL outside of VBA . Any advice or comments would be greatly appreciated.

[malik641]

For moving to VB.NET:
How much of a headache you will have will depend on how structured your code is. If you have a lot of recorded macros, for example, it won't be fun converting that code. Otherwise, I don't think it will be too bad. VBA is basically a subset of VB 6.0 and VB.NET supports a lot of VB 6.0 features (from what I've experienced converting VBA/VB code).

You can probably get away without converting much with Visual Studio Tools for Office:
http://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office
http://msdn.microsoft.com/en-us/office/aa905533.aspx

XLDennis does pretty extensive work in VSTO:
http://xldennis.wordpress.com/
http://www.xtremevbtalk.com/showthread.php?t=160459
http://www.excelkb.com/article.aspx?id=10185

Yes, the userforms will be a problem. You'll have to redevelop the forms, but most of the code should remain intact and ready-to-go, with some minor adjustments (all the object event handlers are completely different, for example).

Personally, I like VB.NET much better than VBA. VBA isn't as object oriented as VB.NET.


For moving to C#:
Can't really help you there. In reality, all the code will have to be converted / rewritten. That will not be fun if you're not familiar with C#.


I hope this info helps. I haven't done much of converting VBA to VB.NET. I'm sure there will be some others who will chime in to help. Check out those links in the meantime.

Good luck!

[JWhite]

Thanks for that. No, I haven't used recorded macros and it is fairly structured. The Userforms are a concern but they're not really the "meat" of the application. A limited amount of information is input through the forms and then the application goes off and does a lot of stuff with workbook connections, pivotcaches, and pivottables. From what I gather, that part may be fairly similar in Visual Basic. C# just seems like too steep of a learning curve for me at this point.

I am concerned by a phrase in the Microsoft Developer's forum on the subject of converting from VBA to Visual Basic.Net. They say to stick with VBA "When you want to use Excel custom functions". Any idea what they mean by that? I've got some UDF's that are a critical part of the product and I do depend on Excel functions for some of what I do. Any idea what they mean by "Excel custom functions"? Is there some limitation on writing UDF's in Visual Basic?

Here's the link to the MSDN article: http://msdn.microsoft.com/en-us/library/aa192490(office.11).aspx.

[JWhite]

MALIK641 - Forgot to thank you for all the links included in your reply. Looks like some useful sources that I hadn't found on my own.

[malik641]

No problem about the links. After working with Excel and VBA for 3+ years you end up with good resources :) And I'm happy to share (saves headaches people can do without).


They say to stick with VBA "When you want to use Excel custom functions". Any idea what they mean by that? I've got some UDF's that are a critical part of the product and I do depend on Excel functions for some of what I do. Any idea what they mean by "Excel custom functions"? Is there some limitation on writing UDF's in Visual Basic?
I guess they mean to say any function in another Add-In. An example would be the NetWorkdays function. You most-likely cannot use stuff like that. You can still use Excel's built-in functions like Find(what:="somedata",after:=Range("B2")) and anything under Application.WorksheetFunction. This is assuming you will be using VSTO and that your code will run from Excel.

One question, is this code still going to work from Excel? Or is everything going to be your own GUIs? (I should have asked this sooner).

If the former, then I believe you can still use your UDF's with VSTO...but I'm not completely sure. If the latter, then you will most-likely have to find a way to rewrite them to accept Arrays rather than the Range object. This is just my guess because I have never made a VB.NET project that used to be a VBA project.

[malik641]

You know...I never paid much attention to VSTO until now. I'm going to look further into this. I would love to start writing VB.NET code that can be run in MS Excel. This could be fun :)

[JWhite]

You asked "is this code still going to work from Excel?". It will still be an Excell add-in, if that's what you mean. It's a relief to hear that the Excel standard functions can be used. I had assumed they would be until I read that Microsoft article and then I wasn't sure.

Other than the Userforms my biggest concern is that my VBA add-in makes use of the worksheet in the add-in. Basically I use it as a "scratch-pad" where I store configuration and scheduling data that I load from an ERP database. This data is used in drop-down selections on the menus. I gather that when I'm running from a DLL that I'm not actually running from my own hidden Excel workbook. So I'll have to find somewhere else to keep this data so it's still accessible to the new menus.

I'm sure I'll figure all this out (obviously other people have) but at this point I'm just trying to get an idea how big the project is and whether it's the right way to go. I appreciate your feedback.

[Bob Phillips]

Just add a workbook, and use that.

[JWhite]

I like the idea of adding a workbook. Since it doesn't carry over information from one session to the next I could create it once for each user's session. I guess I could even make it an add-in workbook so it would be hidden from the user then discard it at the end of the session.

That would make the transition SO much easier since I have so much code that addresses cells in my add-in workbook.

[Kenneth Hobs]

Excellent links Malik. Not many forums out there for vb.net and excel. Might be a good subforum.

[Bob Phillips]

I like the idea of adding a workbook. Since it doesn't carry over information from one session to the next I could create it once for each user's session. I guess I could even make it an add-in workbook so it would be hidden from the user then discard it at the end of the session.

That would make the transition SO much easier since I have so much code that addresses cells in my add-in workbook.

I wouldn't bother with the addin, just hide the workbook, and discard it at the end.

Here is some sample code, it isn't VB.Net or even automation, but the principle is the same



Dim wb As Workbook

Set wb = Workbooks.Add
Windows(wb.Name).Visible = False
wb.Worksheets(1).Range("A1").Value = Now
MsgBox wb.Worksheets(1).Range("A1").Value
wb.Close savechanges:=False


You get a small flicker, but nothing untoward.

[malik641]

Excellent links Malik. Not many forums out there for vb.net and excel. Might be a good subforum.
Thanks. Actually, I think there was some talk about adding a VB.NET subform a while ago, but I guess it didn't get anywhere. We can make a poll to see if anyone wants it (in the Site/Forum related subform). What do you think?


And for using a workbook, in addition to xld's method you could also use:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add

xlWB.Worksheets(1).Range("A1").Value = "hey!"
xlWB.Close savechanges:=False
xlApp.Quit

But this will be slower since an entirely new excel application must be stored in memory, but there will be no flicker. And it's a little more to manage. If you can, stick with xld's method.

[Bob Phillips]

Thanks. Actually, I think there was some talk about adding a VB.NET subform a while ago, but I guess it didn't get anywhere. We can make a poll to see if anyone wants it (in the Site/Forum related subform). What do you think?


And for using a workbook, in addition to xld's method you could also use:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add

xlWB.Worksheets(1).Range("A1").Value = "hey!"
xlWB.Close savechanges:=False
xlApp.Quit

But this will be slower since an entirely new excel application must be stored in memory, but there will be no flicker. And it's a little more to manage. If you can, stick with xld's method.

I had assumed that if he is using VB.Net Joseph, he would be using automation, I just didn't do the grunt work.

I vote for a .Net forum.

[malik641]

I had assumed that if he is using VB.Net Joseph, he would be using automation, I just didn't do the grunt work.

I vote for a .Net forum.
Gotcha.

And I vote yes as well!

[Kenneth Hobs]

I would vote yes.

[malik641]

Ok. Poll posted here (http://vbaexpress.com/forum/showthread.php?t=22381).

[JWhite]

Thanks for the tips on how to create hidden workbooks. I didn't know either technique but they're both easy to code and would be easier than working with an add-in. Thanks a lot.

And I voted a BIG yes on the VB.net forum. Perfect timing for me. I would mark this thread as "Solved" except that I hope more people look at it and vote YES on the VB.net forum.