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.
Pages to are hidden for
"Vba Range Function"Please download to view full document