How do I call a LabVIEW created DLL from within Microsoft Excel (VBA)?

[RichardBallantyne]

Hi,

I'm trying to get Microsoft Excel 2003 to call a function stored in a dll that I created using LabVIEW 7.1.  The function is very simple and is called "binary_file_to_ascii".  Basically it just reads a LabVIEW created binary spreadsheet file and outputs the columns as float arrays.

The function arguments are as follows:

INPUT : String that contains the path to the binary spreadsheet file

OUTPUT : Number of rows in the spreadsheet

OUTPUT : Number of columns in the spreadsheet

OUTPUT : Float array containing the elements in column 1

OUTPUT : Float array containing the elements in column 2

..

...

OUTPUT : Float array containing the elements in column 11

However, for some reason there is something wrong with my VBA code and/or the way I created the DLL in LabVIEW because Excel crashes when it loads my DLL.

What do I need to change in my VBA code or my LabVIEW DLL build settings to fix this?

I've attached to this post a ZIP file that contains all my code and a sample binary file.

Your help with this will be much appreciated.  Thanks!

Richard

P.S.  Here is the VBA code I'm using:

Private Declare Function binary_file_to_ascii Lib "C:\temp\binary_file_to_ascii\binary_to_ascii.dll" _
(ByVal binaryFile As String, ByVal nrows As Long, ByVal ncols As Long, ByVal column1 As Variant, _
ByVal column2 As Variant, ByVal column3 As Variant, _
ByVal column4 As Variant, ByVal column5 As Variant, _
ByVal column6 As Variant, ByVal column7 As Variant, _
ByVal column8 As Variant, ByVal column9 As Variant, _
ByVal column10 As Variant, ByVal column11 As Variant)
                    
Private Sub load_binary_spectra()

  Dim nrows, ncols As Long
  Dim col1(), col2(), col3(), col4(), col5(), col6(), _

      col7(), col8(), col9(), col10(), col11() As Variant
  Dim lRetVal As Long
  Dim spectra_path As String
   
    spectra_path = "C:\temp\binary_file_to_ascii\sample_binary_file\sample_binary_file.dat"
    lRetVal = binary_file_to_ascii(spectra_path, nrows, ncols, _

              col1(), col2(), col3(), col4(), col5(), col6(), _

              col7(), col8(), col9(), col10(), col11())
    MsgBox (spectra_path)
End Sub

Message Edited by Richard Ballantyne on 07-25-2007 02:39 PM

Message Edited by Richard Ballantyne on 07-25-2007 02:39 PM

[mikeporter]

I've never done that sort of thing with a DLL. In the past I have built the VI as an ActiveX server and it works real well... There's also an example that ships with LV.

Mike...

[kaêm]

Hi,

I don't know anything about Visual Basic, but here's my guess.

When you load the DLL with this line of code

Private Declare Function binary_file_to_ascii Lib "C:\temp\binary_file_to_ascii\binary_to_ascii.dll" _
(ByVal binaryFile As String, ByVal nrows As Long, ByVal ncols As Long, ByVal column1 As Variant, _

You are defining how the parameters are sent. It seems that all are sent by value (ByVal), but in the DLL header, some of the function's
parameters are defined as pointers(see the function prototype below, look for asterisk(*))

void __stdcall binary_file_to_ascii(PStr binaryFile,
                                                long *numberOfRows,
                                                long *numberOfColumns,
                                                float column1[],
                                                float column2[],
                                                float column3[],
                                                float column4[],
                                                float column5[],
                                                float column6[],
                                                float column7[],
                                                float column8[],
                                                float column9[],
                                                float column10[],
                                                float column11[]);

Can you define in VBA that you want to use pointer instead? Maybe by using ByRef (as by reference).

Hope this helps.

[RichardBallantyne]

Thank you for your responses.  Even if I modify the VBA code so that it uses ByRef instead of ByVAL, Excel still crashes.

Now the declaration looks like this:

Private Declare Function binary_file_to_ascii Lib "C:\temp\binary_file_to_ascii\binary_to_ascii.dll" _
(ByVal binaryFile As String, ByRef nrows As Long, ByRef ncols As Long, ByVal column1 As Variant, _
ByVal column2 As Variant, ByVal column3 As Variant, _
ByVal column4 As Variant, ByVal column5 As Variant, _
ByVal column6 As Variant, ByVal column7 As Variant, _
ByVal column8 As Variant, ByVal column9 As Variant, _
ByVal column10 As Variant, ByVal column11 As Variant)

When a LV vi is compiled as a DLL, why does LV change the vi signed int32 output to a pointer to a long whenever the vi is compiled as a DLL?  Shouldn't it just be a long and not a pointer to a long?

[kaêm]

You propably need to define the variables nrows and ncols as pointers.
See this link about pointers in VBA, especially the part about VarPtr.

And to your other question about why LV compiles the VI to use pointers
in the DLL. This is because a function can only return one value that is
defined with the return value in the application builder. If you wish to output
more values, you'll have to use pointers. You can read an article about
subroutine in wikipedia, if you like to get more info on this matter. The chapter
about stack is very useful.