Pluses and minuses of programing with VBA vs. XLL
Chris
minuses of programing in VBA vs. XLL? I would be happy
to get them so I can understand the pluses and minuses of
the two. I'm mostly interested in making math functions
and subroutines.
I find the xll api interface to be a very quick way of
transfering data back and forth from excel. You can pass
a single cell or a bunch of ranges and return them
rapidly to excel. There is also only two function
necessary to learn XlAutoOpen and Excel4, plus a good
understanding of the xloper data structure. I've coded
xll's in c/c++ and fortran(converted xlcall.h to
xlcall.f90 fortran code and made a fortran library for
xlcall32.dll).
I've also coded some c/c++ and fortran and interfaced
them to excel via VBA. I find it to be a bit slower
transfering the data back and forth from excel. It's
also a bit harder to send a range adds a few seconds to
the process.
Thanks for your opinions
Chip Pearson
There are several advantages of XLLs, including speed of execution (compiled
code vs interpreted code), code security (no source is distributed to the
users), and the features of C (pointers, etc). The disadvantage of XLLs is that
they are based on the XLM (version 4) macro language, and so you are largely
limited to those functions. You have very limited access to the Excel object
model, and can't take advantages of most modern Excel features and tools.
For high performance User Defined Functions, those that need to be optimized for
execution speed, then XLLs are the way to go. However, you can't do much in the
way of real automation with XLLs.
> There is also only two function
> necessary to learn XlAutoOpen and Excel4, plus a good
> understanding of the xloper data structure.
That's true, as far as it goes. However, the reason all you need to know is
Excel4 is that you are limited to XLM commands. You don't have to learn the
much more complicated Excel object model because you can't use it. XLLs are
quite limited in what they can do.
You can get the best of both worlds by writing a COM Add-In (CAI) in C/C++ or
any other language you like that supports COM. A CAI is simply an ActiveX DLL
that implements the IDTExtensibility2 interface. (CAIs are not supported in
Excel 97 or earlier, and you can't call their function directly from cells in
versions other than 2002.)
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Chris" <crt3...@msn.com> wrote in message
news:0d1f01c2723b$2278e360$3aef2ecf@TKMSFTNGXA09...
Chris
there set up. The code is a bit bulky and there are to
many "Microsoft functions" to learn. It would be a huge
undertaking to learn and I'm just not interested.
So your saying that if I want to add math functions and
subroutines and pass cell data back and forth that xll is
the way to go.
But if I want to want to make excel "come alive" to use
VBA. For things like transfering data to and from
databases and using Real Time data from servers and, etc.
>.
>
Chip Pearson
> I looked a little at COM add-ins and I don't really like
> there set up. The code is a bit bulky and there are to
> many "Microsoft functions" to learn. It would be a huge
> undertaking to learn and I'm just not interested.
Yeah, but what are you gonna do. That there are many "Microsoft functions" to
learn when dealing with an MS Office program isn't a terrible surprise. CAIs
aren't much bulkier than XLLs. The CAI model is based on the IDTExtensibility
interface, supported by all Office programs. A single CAI can be written to
support all of the Office apps, while XLLs support only Excel. The code
determines at run time what app is connecting to it, and takes the appropriate
action.
> So your saying that if I want to add math functions and
> subroutines and pass cell data back and forth that xll is
> the way to go.
Yes. It is very fast and very efficient. Generally, XLLs are really for
worksheet functions, and they are very good at it. But that is really all they
can do.
> But if I want to want to make excel "come alive" to use
> VBA.
Broadly speaking, that's correct, although I wouldn't limit the options to
simply VBA. It would be more accurate to say that if you want to make Excel
"come alive" then you need to use COM, in the language of your choice. VBA is
the easiest language in which to automate Excel, but it is by no means the only
one.
> For things like transfering data to and from
> databases and using Real Time data from servers and, etc.
That can be done, to some degree, with XLLs, but you have to consider the
interface between your code and the Excel application. You are really limited
to 3 options -- 1) VBA, which in contained in a workbook, 2) XLLs, and 3) COM
Add-Ins. These are the only methods that you can directly get your code to work
with Excel.
You can, of course, write standard Windows DLLs in the language of your choice,
but you'd need provide VBA "wrapper" functions to call those functions.
For example, suppose you want to write some function that is math-intensive,
call it ChrisCalc. You can write that function within an XLL, register the
function with Excel, etc, and call it directly from a worksheet cell. ChrisCalc
can do whatever it wants with external databases, real time data, etc. But the
only way that code can "talk" to Excel is via cells and the Excel4 procedure.
Or, your could write the code in C/C++, implement the IDTExtensibility
interface, and (in Excel 2002) call those functions directly from cells. In
this case, you'd have full access to the Excel object model. Or, you could
write the ChrisCalc as a standard Windows DLL, and provide VBA code with the
appropriate Declare statement and pass along arguments from VBA to the DLL.
You're certainly not limited to VBA as a language choice, but you are limited to
how your code, written in whatever language, will communicate with Excel.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com
"Chris" <crt3...@msn.com> wrote in message
news:0ce801c27244$d5198fc0$35ef2ecf@TKMSFTNGXA11...