Docstoc

Vba Range Function

Document Sample
Vba Range Function Powered By Docstoc
					                                              IEOR 4500

                             Calling a C/C++ DLL from VBA
A great use for VBA is that of a “bridge” between Excel and a C++ DLL. Excel contributes its flexibility,
graphical capabilities and well-understood interface. Non-specialists can use Excel, but might on the other
hand find it difficult to code up a call to a sophisticated subroutine.

VBA does have some programmatic capabilities of its own, of course, but these pale in comparison to what
we can do with C++. In addition, the development features available in VC++ make it a far better
environment to develop a complex application than VBA.

So we leverage the best features of all three environments: we use Excel as the source of data, which the
user passes to a VBA macro, which in turn passes it to a C++ DLL, where all the serious computation takes
place. VBA is smart enough to understand the intricacies of Excel, and can gather the user data into a
format that is suitable for passing to the DLL – this is the preferred function for VBA.

We illustrate this with a simple example. In this example, the user selects a range of cells, which get
passed to a macro. The macro then organizes the cell values into an array. The array is passed to a C++
DLL, whose function (in this simple example) is to compute the norm of the array (the square root of the
sum of squares of entries in the array). Of course, one could substitute “norm” with something far more
complex – the purpose of this exercise is to expose the mechanics.

So, to begin with, create a C++ DLL that computes the norm of a vector. To fix ideas, in this write-up we
will call the DLL “norm.dll” and the particular function that computes the norm will have the prototype:

double norm(double *vector, int n);

Here vector is the array whose norm we are computing and n is its length. We will make use of this
function at the end of the exercise, but it’s good to have it ready (and debugged) now. In general, we
should have our complex mathematical subroutines fully set-up and tested before we integrate them into a
graphical environment.

Now let’s go back to VBA. First we will create a VBA macro that takes a range input by the reader – that’s
it. Type Alt+F11, and type:

Function mynorm(ByRef Arr As Range) As Double

Then hit a carriage return. VBA will automatically add an End Function in then next line. Also, you will
note that as you type the function declaration above VBA will “assist” you. For example, when you type
an “As” a little window will pop showing you the possible choices.

By now you should have consulted the Walkenbach book, but here’s a quick explanation of the above
declaration. We are declaring a function whose name is mynorm. The function will return a double
precision number – this is the final As Double in the declaration. The function takes a single argument: a
Range with name Arr. In VBA, arguments are passed either using the qualifier ByVal (by value) or using
ByRef (think of this as passing the memory address). Ranges must be passed using ByRef. Between the
two lines of code add the following line: mynorm = 0, which means that (for the time being) the function
simply returns a 0.

Next, we will organize the values in the cells of the range into an array. Let’s suppose that we know that
the range will have exactly 10 cells (later we will change this). Then the code would be:
Function mynorm(ByRef Arr As Range) As Double
  Dim vals(0 to 9) As Double
  Dim r As Range
  Dim i As Integer
  For Each r In Arr
    vals(i) = r.Value
    i=i+1
  Next r
  mynorm = 0
End Function

In summary: we declare and allocate an array vals of 10 doubles, and we also declare a range r and an
integer, i. Then we loop through the cells in Arr, and store the cell values in the vals. Save this code, and
compile it to be sure it’s OK (in Debug -> Compile VBA Project). Now test the code. Go back to the
Excel worksheet, put numbers in cells, say A1 through A10, then click on an empty cell, and in it type:
= mynorm(A1:A10)
You should see a zero appear in that cell.

Quick aside: how do we debug a VBA macro? In the VBA editor, go to View->Toolbars, and make sure
that Debug is checked. This will give you the Debug toolbar, and you can use that to set breakpoints and
advance the execution. Do this, and test the macro again – you will see how the vals array is properly
filled. Question, what happens when a cell in Arr is actually empty?

OK. Now we will modify the code so that Arr can be of any size. The code to do this will look like the
following:

Function mynorm(ByRef Arr As Range) As Double
  Dim vals() As Double
  Dim mylen As Integer
  Dim r As Range
  Dim i As Integer
  mylen = Arr.Rows.Count
  ReDim vals(mylen)
  i=0
  For Each r In Arr
    vals(i) = r.Value
    i=i+1
  Next r
  mynorm = 0
End Function


Here we have changed the declaration of vals – stating it as vals() means that we don’t know in advance
what its length will be. We also declare a new integer, mylen, which will be used to hold the count of cells:
this is done in the statement mylen = Arr.Rows.Count. Finally, ReDim vals(mylen) sizes the array vals
so as to have length mylen (Note: ReDim can be used to truly resize an existing array – but then the current
contents are wiped out. There is an alternative way of resizing an array that preserves the existing entries).

You should again save, compile, and test the macro.

We are now ready to call the C++ DLL. First we need to declare the function. At the top of the macro, we
type the following:
Public Declare Function norm Lib "c:\exports\norm.dll" (ByRef x As Double, ByVal n As Long) As
Double

In this declaration, don’t worry what the initial “Public” is (or, consult the Walkenbach book). An
important part of the declaration is the Lib "c:\exports\norm.dll" statement: this tells VBA where to find
the DLL (it can be anywhere you want to put it – the above is just an example). Next come the arguments.
In the declaration, x is the name of the array whose norm we will compute. As discussed before, this is
passed ByRef (after all, this will be passed to a subroutine that expects a double* argument, so an address
should be passed here).

But the fact that we are passing the length of the array, n, as a “Long” should come as a surprise. After all,
our DLL expects an int, not a long. What is happening? Simple: what C++ considers an int is an 4-byte
integer. In VBA, this would be considered a long.

Having declared the function we can now call it. We replace the mynorm=0 statement with:

  mynorm = norm(vals(0), mylen)

Now this is a strange statement. Why vals(0), and not just vals, as the first argument in the call?
“Simple”: when passing something ByRef, VBA proceeds as follows: it takes the argument you typed,
computes its address, and that is what gets passed. Recall that in C, an array is stored at the memory
address occupied by its first (that is, zeroth) entry. So the memory address of vals(0) is exactly where the
array begins, and the call above is correct. Still, you may wonder – the call is correct, but why bother?
Why would it be wrong to simply use vals as the argument? That is because in VBA (unlike in C) vals
would just be the name of the array, and not its memory address.
[For further background, see: http://support.microsoft.com/kb/207931, “Scenario 1”.]

Save and compile. Now test. What happens? An error message in the desired target cell! What happened?
If you put a breakpoint in the VBA code just where norm gets called, you will see that it is exactly at this
point that the error message appears in the Excell cell.

Let’s recall the prototype we used when we created the DLL: double norm(double *vector, int n); . The
catch is that we compiled this DLL as a C++ DLL – a byproduct of this is that the name of the function
actually gets changed: to the world outside the C++ compiler, the function is not named norm, but named
something else entirely. This is an example of the so-called “name-mangling” caused by C++ compilers
(it’s not just a “feature” of VC++). In summary: the error occurs because VBA cannot find the norm
function.

In order to remedy this, quit Excel (hopefully after saving the code), restart VC++ and change the prototype
of the function to:

double _stdcall norm(double *vector, int n);

Here “_stdcall” instructs the C++ compiler to refrain from name-mangling. Save and recompile the DLL,
and copy the DLL to the directory where the VBA code expects it to be. In our example this was
c:\exports. Then restart Excel and reload the worksheet you had before. The VBA code should now work.

				
DOCUMENT INFO
Shared By:
Tags: range, function
Stats:
views:132
posted:6/29/2009
language:English
pages:3