How will we create UDF's on 64 bit OS after support for VBA is dro

[Don]

I read somewhere that Microsoft plans to drop support for VBA/VBE in office
apps in future releases of office for 64 bit OS. Remembering how fast the
uptake of 16 to 32 bit systems was, I figure it won't be long that most
office workstations will be 64 bit OS, so I decided to learn about VSTO.

One of the first things I learned was that you can't create UDFs in VSTO -
you have to use VBA (while VBA can interact with VSTO objects, you still need
VBA).

So, the missing piece of info I have not been able to track down is how we
will be able to create UDF's in excel for environments where VBA is no longer
supported. Perhaps I have misundestood something I read?

 

[Chip Pearson]

Create a VB.NET Class Library and write your functions in that library. This
is straight NET, no VSTO required.
http://www.cpearson.com/Excel/CreatingNETFunctionLib.aspx

Personally, I think NET will be MUCH more tightly integrated with Office
(not via VSTO/SE, which strikes me as something of a hack job to get
*something* NET-related into Office) long before we see the demise of VBA.
(Remember, XLM is/was supported 12 years after is was made "obsolete" by
VBA.) There are millions upon millions of lines of VBA out in the world. I
doubt MS would abandon all of that code. VB6 is still fully supported (as in
it works, not as in MS provides tech support or updates) 6 years after is
was made obsolete by NET.

It is just my opinion, but NET has a long way to go to make it easily
integrated with Office.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Tony Gravagno]

Chip, please forgive if you've already seen this inquiry recently.

At the bottom of your article you mention using regasm in the
deployment process. Few people (except maybe Phil Wilson) discuss
deployment at all, so it's great that you have that material. I
haven't been able to deploy to any non-development system even after
using regasm. I was able to deploy to other development systems and
everything worked fine.

See my blog link below. In article 2 of my series documenting my
effort, I link to an MSDN article on Registration-Free COM. Whether
using manual or automatic registration I still couldn't get my managed
code to execute from Excel. Given that MSDN article, do you still
recommend regasm or can everything really be handled with an Isolated
flag from VS2005?

Can you verify that you've deployed to basic end-user systems using
the info provided on your page?

A COM Shim is highly recommended by many MVPs and Microsoft people.
But you don't mention shims at all. (My blog describes shims and why
they're supposed to be helpful, for anyone interested.) Misha
Shneerson wen't so far as to say "Regasm’ing is not good enough for
addins". Any comment?

From all of the reading I've done (a lot) it seems like there are a
lot of people who can't deploy this architecture, but the answers from
all of the professionals are all the same, leading to the same sites.
I haven't seen a single confirmation that someone has been able to do
a successful rollout, given the common responses like "read Chip's
article", or Eric's, or Phil's, or Andrew's, or... Maybe everyone
winds up going back to XLL/XLA/VBA?

In short my best guess at the moment is that deployment to a system
with just the .NET Framework Redistributable is different than
deployment to a system with the .NET Framework SDK.

I reworked my code to use a COM shim and still haven't had any luck,
with or without manual regasm and even regsvr32 of the COM shim. I
have a complete tutorial on my blog which includes lots of links to
sources of information on this interesting topic:
remove.this.munge.pleaseNebula-RnD.com/
blog/tech/2007/11/excel-tools5.html
Part 5 is the tutorial but it stops short of explaining how to do a
successful end-user deployment. Parts 1-4 have good intro material I
gathered during the learning process.

I call my blog series "Mine Field of Excel Development Tools" because
there is so much beyond the basics that get published in articles and
forums. This isn't as easy as writing a little code and then using
=MyFormula(). Developers need to learn about data typing between
managed code and the basic Excel typs. they need to consider whether
they should implement IDTExtensibility2 - and if they do they'll
probably wan to override methods like Equals() and GetHashCode() which
no one discusses. They need to know if and when to implement an
Interface for COM, and what flags to set for Setup/MSI packages. And
then, leading back to the OP, is the future with XLL? VSTO? Excel UDF
Services? Some as-yet-unnamed middleware/API in development? Third
party offerings like ExcelDNA, ManagedXLL? or Add-In Express?

Ahem... this is really fun stuff, but simple it aint.

Thank you kindly.
Tony Gravagno
Nebula Research and Development
TG@ I.always.munge.my.domainNebula-RnD.com

 

[Don]

Thanks. Your web site will be quite helpful. Although I have a few years
expereince using VBA I am new to Visual Studio and know nothing of windows
programming or .NET. For sure there is lots of info out there - right now
I'm "drinking from the firehose".

 

[Chip Pearson]

If you are really going to move to the NET world, you should wait for Visual
Studio 2008, due out, I think, Q1 2008. It comes standard with a new
version of VSTO integrated into the IDE. You'll get a lot more templates,
depending on what Office programs you have installed. In VS2008, I get 14
Office 2007 project templates and 10 Office 2003 project templates.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Don]

Actually VS2008 was released last month. I've just downloaded the 90 day
trial and I will likely purchase an MSDN subscription before it expires. At
first glance, I sort of appreciate the increased security from the point of
view of someone tyring to distribute new office addins as a commercial
product, but OMG it is complex compared to making a VBA xla file available
for use on a public or shared drive among a trusted community of people all
working together in the same company. While I agree backwards compatibility
with VBA will be around for a long time, I also believe it will be a
relatively short time before we are all running 64 bit desktop OS and there
will likely be new features we need access too that will never be back ported
to VBA, so I want to started on what appears to be a rather steep learning
curve.

It's kind of a deja vu experience ... I remember at first thinking windows
cmd scripting capability was useless compared to unix. Over years I've
learned it is actually very useful but full of unnecessary complexity and
inconsistencies. My first experiences with VBA are better, but it too has
it's strange behaviors (default methods, extremely obtuse and of questionable
utility "on error" model and such) but I have grown to live in comfort with
it, and use various additional features through other libraries (like regex
and scripting.doctionary) that make it very powerful. I'm sure I'll be there
with Com/vsto in a while ... but probably not a short while

 

[Keith R]

Does anyone know if the 2008 express editions include the office templates?
<fingers crossed>

Thanks,
Keith