RiskGen project example

[sysmod]

I've now worked through pages 1-8 of RiskGen Port Log.docx (still have
to do Packing) and am wondering...

1) You had to create a Public Module GlobalHelper. Does that have to
be done for every project? If so, is there a template one I can just
include? Or was that just because the code had poor practice like an
unqualified Range() reference rather than say Activesheet.Range()

2) Is there any particular reason why NetOffice has to use get_Range
rather than just Range?

3) I'm using VS VB Express so there is no source control, and no
Resharper. I downloaded Dev Express CodeRush and will see what it
does.

4) You moved WatchOutPut() into the .DNA file. Why?

5) I downloaded the VBA_Risk.xlam file from the author but it seems
incomplete - eg it refers to RiskyDiscrete in a function where it
looks like it should be RiskGenDiscrete ; that would not even compile.
It is also missing the routines GenerateHistogram, ArrangeMyCharts,
ChartIt.

6) In your project RiskGen.NET there is no underlining of
ActiveSheet.UsedRange
Your Module GlobalHelper.vb defines

ReadOnly Property ActiveSheet As Worksheet
Get
Return Application.ActiveSheet
End Get
End Property

But If I use Application.ActiveSheet.UsedRange it is flagged with
Error 1 'UsedRange' is not a member of
'LateBindingApi.Core.COMObject'.
What’s going on?

7) If I am only targeting one version of Excel - eg 2010 - do I need
NetOffice at all?

[Govert van Drimmelen]

Hi Patrick,

1. I created the GlobalHelper class to see how compatible the VB.NET
can be made, so that I have to change as little as possible from the
VBA code. VB.NET makes the members of a Module available throughout
the project, as VBA does to some of the classes in a COM type library.
In Excel VBA the Application class is global (you can see its members
in the <globals> part of the COM type library in VBA). Likewise all
the enum members in the type library can be accessed as global
constants.

So, as you point out, the GlobalHelper allows you to keep the VB.NET
as ActiveSheet.XXX instead of Application.ActiveSheet.XXX. It's not
something you need, but I wanted to experiment a bit to figure out
what could make the VBA -> VB.NET transition easier.

For this example I just filled in the methods and enums in the
GlobalHelper as I went along.
I discussed a bit with Sebastian Lange (of NetOffice) how one could
generate the GlobalHelper class automatically from the type library.
It sounded promising.

2. & 6. The way COM properties are exposed from the NetOffice
assemblies currently aren't great. This is because NetOffice is
generated as C# code, and C# has some limitations in how properties
can be defined. In particular, indexed properties and those that take
parameters can't be defined in C# to match the way they can be defined
in VB.NET or the COM interop libraries. This means some properties,
like UsedRange, need to be called as get_UsedRange in the current
version of NetOffice. I'm hoping Sebastian can improve the property
handling in a future version, maybe by generating VB.NET code for his
interop assemblies.
This issue wasn't really obvious before, since the way C# versions
before C# 4 dealt with these properties was problematic, and needed
the explicit get_XXX and set_XXX calls. VB.NET is flexible enough to
define and consume these properties correctly, and C# 4 can consume
them but not define them.

I think the reason the ActiveSheet.UsedRange is not flagged in the
code snapshot is that I switched between different versions of
NetOffice while putting the sample together. I tried to use a newer
version with some improved property support, but had to go back to the
one that only supports get_UsedRange later.

3. The Visual Studio Express editions are not really full-featured
development environments (though I think nicer than the VBA IDE). In
particular they don't support Visual Studio add-ins, so I suspect your
CodeRush won't work there.
If you are looking for a free IDE with many features, have a look at
SharpDevelop (be sure to get version 4.1 or later). It keeps getting
better and I'm happy to support any issues you have with Excel-DNA in
SharpDevelop.

4. WatchOutput() is the RiskGen marker function. I wasn't sure where
to put it and so I left it in the .dna file, which is where Robert had
defined it initially. I'm not sure why he didn't have it in the VBA.
Thinking about it more, it should probably just go into the VB.NET
code.

5. I suspect you got an earlier version of the VBA RiskGen than I me.
I used the one from 9 January here: http://rwdvc.posterous.com/riskgen-test.

7. If you are just targeting Excel 2010 you can certainly use the
Primary Interop Assemblies (PIA) instead of the NetOffice assemblies.
The Excel 2010 PIA assemblies are here: http://www.microsoft.com/download/en/details.aspx?id=3508.
An advantage is that all the property stuff is already right in the
PIAs. A disadvantage is that you might need to install the PIA
assemblies using the downloaded installer - I'm not sure you can just
copy or pack them into the .xll like the NetOffice assemblies. This
also means you need admin rights to deploy your add-in. In general the
PIA assemblies have a reputation for bringing deployment headaches
(http://blogs.msdn.com/b/vsto/archive/2008/05/20/common-pitfalls-
during-pia-deployment-and-installation.aspx).

It's easy enough to try though, apart from the get_XXX and set_XXX
property issues (that NetOffice has and the PIA assemblies don't), you
only need to change the namespace Imports.

If deployment is not a big concern, and you are targeting a single
Excel version, the PIAs are probably the best choice at the moment.
NetOffice might add some more nice features in future though, so it's
worth keeping an eye on even if you don't use it.


Regards,
Govert

[gle...@gmail.com]

Hi Govert,

The thread here is quite old but it is of interest to me.

Unfortunately the word document "RiskGen Port" seems nowhere to be found as the hoster as ceased its activities... :(

Do you have another link/copy to share?

Many thanks,

Guirec

[Govert van Drimmelen]

Hi Guirec,

You can download the project with the porting doc from here: https://s3.amazonaws.com/share.excel-dna.net/RiskGen.7z

It was a just an experiment to see how hard it is to port some VBA code to VB.NET with Excel-DNA.

A much better and more comprehensive migration guide was subsequently written by Patrick O'Beirne, which you can find here: http://sysmod.wordpress.com/2012/11/06/migrating-an-excel-vba-add-in-to-a-vb-net-xll-with-excel-dna-update/

If you are looking to create something similar to @RISK, I'd not recommend starting with the RiskGen example either.

Cheers,

Govert

[gle...@gmail.com]

Hi Govert,

Many thanks for this. 

I am not into creating something like RiskGen but I am interested in any document that would describe a step by step approach in using Excel-Dna and that goes a bit further down the road than the "getting started" which is a bit limited (don't get me wrong I know this is the concept of a getting started and I have been started, and hooked, in less than 5 minutes so it's all good!).

Patrick's document seems to be exactly what I am looking for. Awesome!

Thanks,

Guirec