I want to send an array from Excel VBA into a LabView VI.
The Excel example shows how to send individual numbers and return an array into Excel but not send an array.
Everything tried sends blank arrays into LabView, which can then be modified in LabView and returned to Excel.
A VBA code snippit that does not work is:
' LoadData Macro
' Keyboard Shortcut: Ctrl+l
' This is an example to demonstrate LabVIEW's Active-X server capabilities.
' Executing this macro loads a LabVIEW supplied example VI "Frequency Response.vi",
' runs it and plots the result on an Excel Chart.
Dim lvapp As LabVIEW.Application
Dim vi As LabVIEW.VirtualInstrument
Dim paramVals As Variant
Set lvapp = CreateObject("LabVIEW.Application")
viPath = lvapp.ApplicationDirectory + "\examples\apps\freqresp.llb\DAK Frequency Response.vi"
Set vi = lvapp.GetVIReference(viPath) 'Load the vi into memory
vi.FPWinOpen = True 'Open front panel
paramNames(0) = "Foo"
paramVals = Sheet1.Range("j1:j1000").Value
Call vi.Call(paramNames, paramVals)
This code generates an error - expecting 1D array or variants.
However, if I supply the correct array, then LabView thinks it is empty
'foo' is a cluster
'Array' is a double array
Neither work to receive the data
I've never thought of running LabVIEW as a "slave" to Excel, but I have frequently run Excel as a "slave" to LabVIEW, easily done using the Report Generation Toolkit (but also possible to do using ActiveX calls).
When I first read this Post, my first question (to myself) was "Why would you want to do that?". I'm still having difficulties coming up with a suitable scenario, but in the process, I thought of a possible way to do this:
Assume the Excel Workbook is called "MyExcel". Further assume that the Sheet and row/column ranges of the Excel Data to send to LabVIEW are know. Assume the LabVIEW VI is called "Load Data From Excel". Instead of trying to pass the Excel Data array into Load Data from Excel (which doesn't seem to work), instead pass the Sheet and ranges (since you say you can pass in simpler arguments). Now have the LabVIEW program open the specified Sheet and extract the array of data itself. You've changed the task from "Push the data from Excel to LabVIEW" to "Pull the data from Excel into LabVIEW".
Thanks for the reply but that approach will be difficult. The problem to solve is relatively simple IF LabView could read arrays (it writes arrays easily). Here is the problem:
I want to convolute two large arrays. I have written VBA programs to do this but they take too long - over 30 sec whereas the LabView version take <1 sec IF I could get the data into LabView.
The convolute is part of an optimization program that goes though many loops so that it takes hours to run efficiently AND if I manually change cells in the spreadsheet, then it takes 30sec to come back as VBA does not respect clean cells (for some reason).
I was trying to write a function in VBA to do this calculation via LabView rather than VBA. One way to do this is to write a text file in VBA and read in LabView, then pass the results back or maybe the clipboard as a buffer. That is faster but clumsy. You could also have LabView look for dirty cells and then do the calculation as the server rather than the client. Again, time consuming and cumbersome. I have written pure LabView code to do the curve fitting but prefer to use Excel as that is easier to incorporate and work with the data. You can do convolute using FFTs but Excel does not do that correctly - they CANNOT make their FFTs into a function (VBA functions cannot modify cells in the spreadsheet but only return variables. FFT modifies cells so cannot be called from a function, only a macro, which then needs to be updated manually). Again, I can kluge VBA (real kluge this time) to make FFTs work as a function BUT prefer the LabView solution, if possible, as I am not sure the FFT will actually save time.
I am not sure if the problem is LabView's fault or Excel's (2013) fault that the variables are not set-up properly. LabView claims to be able to do this. Again, you can pass single variables into LabView and read back arrays but try as I might, I cannot get arrays into LabView.
Any help in sending arrays (maybe Labview expects a variant variable and needs to convert it somehow) would be appreciated. Variants are suppose to be able to be converted automatically but maybe there is a bug in LabView (2010) that does not handle input variant arrays correctly (I have tried making them clusters, double arrays, ect). LabView thinks that all the arrays sent by VBA are empty. If LabView modifies the same array, VBA sees that modification so that the addresses appear to be sent correctly i.e. the array "handle" is being sent to LabView or else it could not fill the array to send back to VBA.
If ever this is solved, I will put on-line the solution for others as again marrying both programs will have advantages. Others may find this interesting is marry LabView with Excel so that LabView does some of the heavy calculations that Excel has kluged.
I don't do much VBA, so am not able to test much. However, the LabVIEW Help for Connectivity VIs and Functions says
|Use the ActiveX functions to pass properties and methods to and from other ActiveX-enabled applications, such as Microsoft Excel. Some applications provide ActiveX data in the form of a self-describing data type called a variant. To review or process the data in LabVIEW, you must convert it to a corresponding LabVIEW data type using the Variant To Data function.|
So maybe passing the Arrays to LabVIEW as a Variant is the way to go ...
I haven't tried doing what you are trying to do. However, a quick web search (which you may have already done) on "excel range to vba array" turned up this page http://www.cpearson.com/excel/ArraysAndRanges.aspx which says that arrays from Excel ranges are always 2D arrays. Have you tried setting up your LabVIEW code to expect a 2D array? That would explain the error message saying that it needs a 1D array, if the array you are passing is actually 2D.
Surprisingly, no Google results on this exact topic - hence this discussion.
Yes. I have tried all types of arrays read by LabView except for variants - not sure how to work with those.
Again, I can send single variables but any type of array is blank into LabView but OK out of LabView. In both directions they are variants. Single variables are variants and they do not need any special handling.
I assume that this is some sort of bug but I am not sure if it is Excel or LabView.
The code snippet was a simple array. You can declare an array of variants and a 2D array of variants. Neither work. The system complains if the types are incorrect - i.e. trying to send a single variable into an array or an array into a single variable. Otherwise, it does not seem to care if 1D or 2D.
Interestingly, on return LabView treats the return array as a cluster - a 2 by X array of numbers of variant type.
This works for me, no Variants required (yes, the numeric control is named "Variant" because I started with one, and it needs to be wired to a terminal on the connector pane):
The VB code:
Sub LoadData() Dim lvapp As LabVIEW.Application Dim vi As LabVIEW.VirtualInstrument Dim paramNames(0) Dim paramVals(0) Set lvapp = CreateObject("LabVIEW.Application") viPath = "C:\Temp\ArrayFromExcel.vi" Set vi = lvapp.GetVIReference(viPath) 'Load the vi into memory vi.FPWinOpen = True 'Open front panel paramNames(0) = "Variant" paramVals(0) = Sheet1.Range("A1:A5") Call vi.Call(paramNames, paramVals) End Sub
If you're still having issues, post your LabVIEW code, maybe there's an error there, and not in passing the array from Excel. It seems to work exactly as expected for me. LabVIEW 2012, Excel 2013.
Accidently erased my comments:
What you are doing is basically what I have tried in many ways. I tried again with a simple LV code and still the array is empty (you can actually debug it with the debugger and it will stop in LV if called from Excel and Excel will wait - in case you are interested). This looks like a bug with LV2010 as we both are using Excel 2013. I have LV2014 and will try installing it and keep you posted. I am always loth to upgrade software as it sometime breaks old code that I have been using for years (since LV7).
BTW: How do you post code? Saw it somewhere but I have forgotten.
Yes, I'd expect debugging to work normally when calling a VI through ActiveX.
The way I posted that code is as a "snippet" - a PNG image with embedded LabVIEW code. Select a section of the block diagram and choose Create Snippet from the Edit menu, then upload the saving PNG using the Image button in the forum editor.