VBA vs. VB vs. C++ vs. C#

[ExcelMonkey]

I am consious that this is a VBA discussion group. However I have a few
questions that pertain to the move away from VBA. Hopefully I am not too off
topic here.

Question 1:
Can someone explain to me the difference between creating and VBA Add-in
(xla) in Excel vs creating a Com Add-in vs creating a DLL? My understanding
is that as VBA is the embedded language in Excel, you can create VBA based
xla files (distributable personal workbooks). Whereas pure VB (VB 6.0 or VB
2005) is not embedded in Excel and thus the code which you create cannot be
in the form of an xla Add-in but some other type (i.e. Com or DLL). Is this
correct? The reason I ask this is I am playing around with VB 2005. I am
assuming it would no longer be an add-in. Can it be initiated through Excel
via menus (like an XLA)?

Question2:
I have been assuming that there would be some benefits from moving from VBA
to VB (aside from the negative issues of VB not being the imbedded language
in Excel). One being better security (i.e. strong password protection),
strong regular expression engine, distrubution/install options. Is this
correct?

Question3:
Lastly, I also hear alot about people wanting to create Excel Tools in C++
or in C# (rather than VB). What are people looking to gain by the C++ or C#
route? I have heard that C++ is faster but I don't really understand why and
how this would be evidient in Excel. Are the beneifts of C++ also found in
C#? Why chose C# vs C++?

Thanks

 

[John.Greenan]

[Note - by C++ I mean writing .xlls - not .net managed code]

Question 1:

Pure VB files for use in excel as a COM Add In would be distributed as a
..dll or .exe file. You need to write to the registry to tell Excel that the
addin exists and then it appears in the COM Addins menu bar item (it's hidden
by default).

Yes, a COM Addin can be instantiated through menu bars and so on. If you
write your add in in vb.net then you have to write an interop layer (the
compiler will do the hard work for you) to enable vb.net code to be accessed
by COM.

Question2

Benefits of VB.NET or VB6 vs VBA.
The VB.net or VB6 code will be compiled and the source code can be
protected. Any xla code can be cracked (xla passwords are trivial) and your
intellectual property is available to all and sundry. [for anyone
frightfully techie, yes, you can reverse engineer vb.net code but you try
reading it if it's been properly obfuscated]

VB6 or Vb.net code can be properly versioned with build numbers.

Writing VB6 or vb.net code allows you to get "proper" developers involved -
lots of developers look down on Excel/VBA as a toy, but are willing to work
in vb6/vb.net (and if anyone disagrees with that, hey, that's fine - I've
seen this with my own eyes, so let's not have a flame war).

I would discount the regular expression support - you can use the vbscript
library in vba or vb6 or vb.net so that's not a deal breaker.

Question3:

Writing excel tools in C++ CAN be faster than vba or vb6 or vb.net, but the
interfaces to use are not easy to understand and the xll is not a well
exploited technology - look at commercial vendors that offer excel based
solutions - very few use the .xll

For most uses, most of the time, the benefits of writing code for Excel in
C++ are outweighed by the increased costs in terms of development. Not
always though.

One thing to remember is this: if you leverage the excel application code
base - using application.worksheetfunction. and so on, you can make
applications run really fast since the Excel code is well written and very
heavily optimised. If you write excellent vba and use as much native excel
functionality as possible it will run quickly. If you write bad code in C++
or re-invent the wheel in C++ your code will crawl.

You need to weigh up the following factors :
Cost per developer - usually you can see contract rates indicating
most-->least C++ , vb.net, VB6, VBA
Time to develop - generally you will see fast-->slow VBA ,VB6 , vb.net , C++
Performance - fast-->slow excel native functions, C++, VB6, vb.net, vba
Security most secure-->easiest to crack C++, vb6, vb.net ,vba
Ease of distribution easy -->hard vba=C++, vb6, vb.net

You should view development in any .net language as presenting the same kind
of overheads in terms of CLR and PIA - making sure the right .NET is on the
box and creating the PIA for your add-in.

This is a really huge topic - I am sure many others will have strong
opinions on this. My view is based on working on several projects where
there has been re-engineering of vba code to other languages. For a weapons
grade answer, I suggest writing your code in a .net language (c~, vb.net,
managed c++) for longevity and then wrapping that up in a COM Addin for
linking into Excel.

 

[ExcelMonkey]

Thanks for the response.

1) What do you mean by "PIA"
2) Wasn't sure if you answered this indirectly but why would someone go the
C# route. I know of someone who orignally buit an excel auditing program as
an XLA and they have since moved it to C#.
3) With respect to your comments about regular expressions, my understanding
is that the regular expressions in VBA are limited (can't do look backs).
As such referencing this library gives you some but not all RegExp
functionality in VBA. So if you wanted full RegExp functionality to work
with strings in an Excel File aren't you forced to steer away from VBA and go
the .Net path?

Thanks again for the repsonse.

EM

[Note - by C++ I mean writing .xlls - not .net managed code]

Question 1:

Pure VB files for use in excel as a COM Add In would be distributed as a
.dll or .exe file. You need to write to the registry to tell Excel that the
addin exists and then it appears in the COM Addins menu bar item (it's hidden
by default).

Yes, a COM Addin can be instantiated through menu bars and so on. If you
write your add in in vb.net then you have to write an interop layer (the
compiler will do the hard work for you) to enable vb.net code to be accessed
by COM.

Question2

Benefits of VB.NET or VB6 vs VBA.
The VB.net or VB6 code will be compiled and the source code can be
protected. Any xla code can be cracked (xla passwords are trivial) and your
intellectual property is available to all and sundry. [for anyone
frightfully techie, yes, you can reverse engineer vb.net code but you try
reading it if it's been properly obfuscated]

VB6 or Vb.net code can be properly versioned with build numbers.

Writing VB6 or vb.net code allows you to get "proper" developers involved -
lots of developers look down on Excel/VBA as a toy, but are willing to work
in vb6/vb.net (and if anyone disagrees with that, hey, that's fine - I've
seen this with my own eyes, so let's not have a flame war).

I would discount the regular expression support - you can use the vbscript
library in vba or vb6 or vb.net so that's not a deal breaker.

Question3:

Writing excel tools in C++ CAN be faster than vba or vb6 or vb.net, but the
interfaces to use are not easy to understand and the xll is not a well
exploited technology - look at commercial vendors that offer excel based
solutions - very few use the .xll

For most uses, most of the time, the benefits of writing code for Excel in
C++ are outweighed by the increased costs in terms of development. Not
always though.

One thing to remember is this: if you leverage the excel application code
base - using application.worksheetfunction. and so on, you can make
applications run really fast since the Excel code is well written and very
heavily optimised. If you write excellent vba and use as much native excel
functionality as possible it will run quickly. If you write bad code in C++
or re-invent the wheel in C++ your code will crawl.

You need to weigh up the following factors :
Cost per developer - usually you can see contract rates indicating
most-->least C++ , vb.net, VB6, VBA
Time to develop - generally you will see fast-->slow VBA ,VB6 , vb.net , C++
Performance - fast-->slow excel native functions, C++, VB6, vb.net, vba
Security most secure-->easiest to crack C++, vb6, vb.net ,vba
Ease of distribution easy -->hard vba=C++, vb6, vb.net

You should view development in any .net language as presenting the same kind
of overheads in terms of CLR and PIA - making sure the right .NET is on the
box and creating the PIA for your add-in.

This is a really huge topic - I am sure many others will have strong
opinions on this. My view is based on working on several projects where
there has been re-engineering of vba code to other languages. For a weapons
grade answer, I suggest writing your code in a .net language (c~, vb.net,
managed c++) for longevity and then wrapping that up in a COM Addin for
linking into Excel.




--
www.alignment-systems.com

I am consious that this is a VBA discussion group. However I have a few
questions that pertain to the move away from VBA. Hopefully I am not too off
topic here.

Question 1:
Can someone explain to me the difference between creating and VBA Add-in
(xla) in Excel vs creating a Com Add-in vs creating a DLL? My understanding
is that as VBA is the embedded language in Excel, you can create VBA based
xla files (distributable personal workbooks). Whereas pure VB (VB 6.0 or VB
2005) is not embedded in Excel and thus the code which you create cannot be
in the form of an xla Add-in but some other type (i.e. Com or DLL). Is this
correct? The reason I ask this is I am playing around with VB 2005. I am
assuming it would no longer be an add-in. Can it be initiated through Excel
via menus (like an XLA)?

Question2:
I have been assuming that there would be some benefits from moving from VBA
to VB (aside from the negative issues of VB not being the imbedded language
in Excel). One being better security (i.e. strong password protection),
strong regular expression engine, distrubution/install options. Is this
correct?

Question3:
Lastly, I also hear alot about people wanting to create Excel Tools in C++
or in C# (rather than VB). What are people looking to gain by the C++ or C#
route? I have heard that C++ is faster but I don't really understand why and
how this would be evidient in Excel. Are the beneifts of C++ also found in
C#? Why chose C# vs C++?

Thanks