Test and Measurement Forum

Some success: using USB ADC11/10 ("the unit") via Excel VBA (macro), including controlling Digital Outputs (DOs) and writing readings into a spreadsheet:
Greetings all. I have had difficulty with above and I gathered from various posts that others have too. Therefore submit following in hope that it may benefit someone:

Am using the USB unit rather than parallel port unit because I want to have two Digital Outputs; understand from Pico that parallel port unit comes with only one DO. Am using the unit with Win98SE, Excel 2000. After some grappling with VBA code etc, have had appreciable success. I have usbadc11.dll installed in C:\Program Files\Pico Technology\Pico Full\ via downloadable update/installer PicoFull_r5_15_5.exe (with no copy elsewhere. This subdirectory is included in PATH, I think by the update/installer process.). This .dll is file version 1.0.1.6 modified 9 Oct 2006. Thanks to Michael at Pico Support for a sample workbook file (UsbAdc11.XLS, 25 Sept 2006) which helped a great deal. The successful macro is in file UsbAdc11_5155c.XLS which I have uploaded to the Pico forum.

Some comments on the macro: It is a trial macro designed to take readings from all 11 channels. Latter are connected to points on a series of 11 resistors, giving about 2300mV on Channel 1 and voltages on the other channels descending in 10 equal steps to about 210mV (a variable resistor) on Channel 11. Macro also operates the DOs, which are connected only to voltmeters.

To operate the macro: Display code for frmUser1 as active window in the VB Editor. Press F5 (Run UserForm). When the form comes up, first click button Open ADC, then (once ADC is open, indicated by LED thereon) click buttons Get Readings, DO1 or DO2, in any sequence and/or repeatedly (perhaps allow each run of Sub GetReadings to finish completely before next click. Otherwise, macro/Excel may crash. In fact, setup appears prone to crashes which require restart. Sometimes I've been able to avoid restart, after msg saying Excel has performed illegal operation or suchlike, by going to VB Editor, clicking Run>Reset, and only then clicking OK on the Excel fault msg. Maybe also have to pull USB cord from the USB ADC11 then replace, before restarting Excel and running the macro.) GetReadings results in writing data into the spreadsheet. DO1 or DO2 toggles output between ~3.3V and 0 volt.

When done, ensure DOs are set back to 0V then click Close ADC.

Only deficiency apparent to me so far is that the readings go in incorrect sequence into the columns in the spreadsheet! Ie, as can be seen in the posted file, Ch 11 rdg in Col B, Ch 1 rdg in Col C, etc. Closer investigation: as posted, macro has nSamples = 50. So I imagine that the Sub GetReadings should collect and write 50 "samples" each of 11 channels, ie should write 550 counts, into the array values. However, by using VB Editor's watch window during runs, I noted that the statement:

nValues = UsbAdc11GetTimesAndValues(m_Handle, times(0), values(0), nSamples)

wrote not 550 but 551 counts into the array values. And values(0) was the count not for Channel 1, but for 195mV, Channel 11. Is the count written into values(0) perhaps not for time 0, but for the previous time (in this case -3000us)?

Readings in more "correct" sequence are written into the columns in the spreadsheet if you deviate from the sample macro and adjust certain statements in the macro in UsbAdc11_5155c.XLS:

Cells(i + 4, "B").value = adc_to_mv(values(11 * i + 0)) Change to (11 * i + 1))
Cells(i + 4, "C").value = adc_to_mv(values(11 * i + 1)) Change to (11 * i + 2))
etc up to:
Cells(i + 4, "l").value = adc_to_mv(values(11 * i + 10)) Change to (11 * i + 11))

But do these rdgs then correctly correspond to the times in column A?

I will be grateful if anyone can clear up this puzzle. Is it a fault in my VBA code, or a quirk of the routine UsbAdc11Run and/or UsbAdc11GetTimesAndValues, ie of usbadc11.dll and/or of the USB ADC11/10?

Further comments are in the VBA (macro) code, module 1.

Progress: Re deficiency and puzzle mentioned in post 9/12/06:
My file UsbAdc11_5155c.XLS uploaded along with the posting of 9/12/06 includes VBA Module1, including:

Declare Function UsbAdc11GetTimesAndValues Lib "usbadc11.dll" _
(ByVal handle As Integer, ByRef tims As Long, ByRef valus As Integer, no_of_values As Long) As Long

There is improvement if this is changed at one point so as to read:
.. Integer, ByVal no_of_values As Long) As ...

ie, if argument no_of_values is ByVal , instead of ByRef as will, I understand, otherwise occur by default.

The improvements are that the Function now much more consistently writes:
- values from Channels 1 to 11 into the correct places in the array values
- the correct number of counts, 550, into the array values (also writes correct number of values (50, not 51) into the array times

The macro in UsbAdc11_5155c.XLS crashes with some frequency; maybe this is due to my having set a v long time (150000us for 550 values) in the statement: sampleInterval = UsbAdc11SetInterval(m_Handle, 150000 ... etc
I have progressed to another version with much shorter time per value, in UsbAdc11_5155f.XLS (uploaded to forum along w this posting) where I observed the same improvements. This version seems less prone to crashing and includes also a Sub which uses UsbAdc11GetValues.