using USB ADC11/10 to get data into Excel via VBA (or DDE)

Post your VB and VBA discussions here
Post Reply
inschris
Active User
Active User
Posts: 13
Joined: Sun Oct 16, 2005 2:28 pm
Location: Capetown

using USB ADC11/10 to get data into Excel via VBA (or DDE)

Post by inschris »

Greetings! Earlier I submitted in Pico Technology Forum Index -> General Software Discussions , thread USB Parallel Port and ADC11, and Michael kindly posted a reply there. But it seems the topic fits better under category:
VB incl Excel: Following on from Michael's abovementioned reply (qv):

Thanks, Michael, for the response above. I imagine that the "new dll" which you suggest, with fixed adc11_ready routine, is adc1132.dll included in the package PicoFull_r5_14_6.exe. I have indeed downloaded and installed that package, and have adc1132.dll, 100kB, modified Monday, 24 July 2006 11:13:02, in the subdirectory C:\Program Files\Pico Technology\Pico Full\.

I regret that my previous posting was unnecessarily voluminous and try here to be more concise.

I am now trying to use the Excel example adc1132.xls from r5.14.6 with USB ADC11/10, GFY6448 06Jan06, w driver PICOPP.SYS V1.12, in win98SE, Excel SR-2. Machine is a Celeron 2.4GHz, w 256MB RAM.

I have adapted the code in that adc1132.xls to allow for using the USB ADC11, also eg in accord w indications in Excel's VBA help. The code can be seen in file ADC1132_5146h.XLS which I will add hereto as attachment.

There are now two versions of adc1132.dll at issue:
A) adc1132.dll from r5.14.4 with size 80kB and date 15/6/2006, 09h59.
B) the "new" adc1132.dll from r5.14.6 with size 100kB and date 24/7/2006, 11h13.

With the stmt Call adc11_get_times_and_values ... commented out, I find that the code runs OK, including stmnts:
If opened Then
Cells(17, "h").value = adc11_ready()
. . .
Call adc11_run(100, 0) ' per PicoForum->General Software Disc'ns/USB Parallel Port and ADC11
While adc11_ready() = 0
Wend
Cells(17, "h").value = adc11_ready()

indeed this is the case regardless which of the two versions of adc1132.dll is in \Pico Full\. Ie, when I step thro, the value in cell 17H changes from 0 to 1 on executing stmt Cells(17, etc following stmt Wend. Locals window in the VBA IDE indicates the same. Also when I press F5 (Run), I see the value in cell 17H change from 0 to 1.

So it appears questionable whether there was anything wrong w the ready call.

However, I regret to say that the macro and Excel still crash on executing the stmt
Call adc11_get_times_and_values(times(), values(), 100)

w msg "This program has performed an illegal oper'n"

Using adc1132.dll from r5.14.4, detail is:
EXCEL caused an invalid page fault in
module at 0000:00480000.
Registers:
EAX=ffffffff CS=018f EIP=00480000 EFLGS=00010246
EBX=00480000 SS=0197 ESP=00480000 EBP=00480000
ECX=0062f5c8 DS=0197 ESI=00480000 FS=50af
EDX=00480000 ES=0197 EDI=00480000 GS=0000
Bytes at CS:EIP:

Stack dump:

Using adc1132.dll from r5.14.6:
EXCEL caused an invalid page fault in
module at 0000:012c04a4.
Registers:
EAX=0062f5c8 CS=018f EIP=012c04a4 EFLGS=00010246
EBX=0062f5c8 SS=0197 ESP=005300fc EBP=0053011c
ECX=005301a0 DS=0197 ESI=81914e2c FS=0de7
EDX=bff76855 ES=0197 EDI=005301c8 GS=0000
Bytes at CS:EIP:

Stack dump:
bff76849 005301c8 0062f5c8 005301e4 005301a0 005302d4 bff76855 0062f5c8 005301b0 bff87fe9 005301c8 0062f5c8 005301e4 005301a0 012c04a4 0053038c

So I will be grateful of indication how to avoid such crash; is it possible that there is a problem with the routine adc11_get_times_and_values in adc1132.dll? Am I right in understanding that it is appropriate to call this routine after adc11_ready returns True?


Of course, the package includes also (since PicoFull_r5_14_4.exe) a new usbadc11.dll, 108kB, modified Tuesday, 1 August 2006 10:51:30, which the installer has placed in C:\Program Files\Pico Technology\Pico Full\ and in \Examples\ADC11\, but which isn't mentioned in the manual. There are no suggestions in the documentation or Excel- or VB examples that usbadc11.dll needs to be on hand, and no indications that usbadc11.dll functions must be declared or called. There is only the indication in adc11.hlp "The same ADC1132.dll file can be used in all 32-bit versions of Windows, with both parallel port and USB port products".

I am open to the idea of using DDE instead of the DLL, as the files PLW044.pdf and PLW044.hlp suggest. So I would be grateful of indication where I can find the fuller information re using DDE, from eg VBA or VB. I would want to get readings from perhaps 8 or more channels, mostly w fairly accurate and precise indication of the time of the reading(s), since I want to calculate rates of change, over periods of 5s to 10s.


***MORE OBSERVATIONS
- in win98se, if you try to step thro get_adc11 in adc1132.xls it hangs on executing stmt: opened = adc11_open_unit. LED doesn't light up. You have to use CtrlAltDel to close Excel.
But if you first run Picolog, then close it, then step thro adc1132.xls, then at stmt opened = adc11_open_unit, LED lights after a short delay, and highlight moves to next stmt: If opened Then
and you can step further thro the Sub, apart from get times and values (or just press F5 / Run)

Is it possible to arrange that the stmt opened = adc11_open_unit will work without first running PicoLog?


I regret to be critical, but think it justified to comment on the current system for obtaining updates of software, where a user has to download the entire update package covering the full range of Pico products, moreover multilingual, hence ~24MB in size. In my locality it is not common to have a high speed connection; we rely on 56k dial-up because of the rather greater cost of broadband. Consequently it is problematic to have to obtain a 24MB download. Is it possible to arrange that one can download a package(s) applicable only to the unit(s) which one has, and perhaps (tho less importantly perhaps) to the language required?
Attachments
ADC1132_5146h.XLS
(97 KiB) Downloaded 1107 times

Michael
Advanced User
Advanced User
Posts: 656
Joined: Thu Jul 07, 2005 12:41 pm
Location: St Neots, Cambridgeshire

Post by Michael »

Hello,

There has been a change to the ADC11 dll file. In order to fix the problems you have already outlined, a new dll file has been created for the USB ADC11. This is titled usbadc11.dll

I have attached an Excel example for using this new dll as the changes are significant. The function names have changed and each function now refers th the ADC11 by a handle.

You can see this new format in the USBADC11CON.C file and also in the attached Excel example.

Please ensure you are using the latest software download version.

Best regards,
Michael
Attachments
usbadc11.xls
(36 KiB) Downloaded 1041 times
Michael - Tech Support
Pico Technology
Web Support Forum

inschris
Active User
Active User
Posts: 13
Joined: Sun Oct 16, 2005 2:28 pm
Location: Capetown

using USB ADC11/10 to get data into Excel via VBA (or DDE)

Post by inschris »

Thanks, Michael, for the response 21 August, and for the file usbadc11.xls. I am glad to say that this now runs without crashing, with the file usbadc11.dll in \Pico Full\, and put times and values into the spreadsheet. I will perhaps respond further after some experience of using it.

inschris
Active User
Active User
Posts: 13
Joined: Sun Oct 16, 2005 2:28 pm
Location: Capetown

using USB ADC11/10 to get data into Excel via VBA (or DDE)

Post by inschris »

Thanks again, Michael, for your posting 21 August, and the file attached usbadc11_156.xls (Excel example). Things have improved a lot, but I still experience a small problem.

To recap: I am now working from this Excel example and using usbadc11.dll which you mentioned, from the software r5.14.6 which was still current when I looked after seeing your posting. My converter is USB ADC11/10, GFY6448 06Jan06, w driver PICOPP.SYS V1.12, in win98SE, Excel SR-2. Machine is a Celeron 2.4GHz, w 256MB RAM.

I have been testing this setup on a simple test circuit which is a bunch of resistors R1 to R11 and a potentiometer in series:

Supply____ 2.98V (2 ZnC C cells in series)
|
R1 3k
|______ Ch 1 on USB ADC11
|
R2 1k
|______ Ch 2 on USB ADC11
|
R3 1k
|______ Ch 3 on USB ADC11
|
R4 1k
|______ Ch 4 on USB ADC11
|
R5 1k
|______ Ch 5 on USB ADC11
|
R6 1k
|______ Ch 6 on USB ADC11
|
R7 1k
|______ Ch 7 on USB ADC11
|
R8 1k
|______ Ch 8 on USB ADC11
|
R9 1k
|______ Ch 9 on USB ADC11
|
R10 1k
|______ Ch 10 on USB ADC11
|
R11 1k
|______ Ch 11 on USB ADC11
|
pot 2k, set I think towards 2k
__________|______ GND on USB ADC11
Supply GND

OBSERVATIONS:
- A plausible set of readings is obtained by Picolog, ie:
Time ch1 ch2 ch3 ch4 ch5 ch6 ch7 ch8 ch9 ch10 ch11
Seconds V V V V V V V V V V V
1 2.330 2.136 1.940 1.748 1.554 1.359 1.164 0.969 0.774 0.576 0.382

- Practically the same values are obtained by my adaptation of abovementioned Excel example, ie by usbadc11_156d.xls (which is attached), where all 11 channels are specified in the macro. Following are these values (copied from the spreadsheet):
Time Ch 1 Ch 2 3 4 5 6 7 8 9 10 11
us mV mV
0 381 2332 2137 1941 1748 1558 1358 1162 967 772 576
552 381 2332 2137 1941 1751 1556 1358 1162 967 772 574
1105 381 2330 2137 1941 1751 1556 1358 1162 967 772 574

BUT here is the small problem: As can be seen, the readings are in a slightly different sequence, ie all the values are "shifted" by one place. Rdg of Ch1 appears in column Ch2, rdg of Ch2 appears under Ch3, etc and Rdg from Ch11 appears under Ch1. When I step thro the macro, I can see by means of the Watch window, and after execution of the line:
nValues = UsbAdc11GetTimesAndValues(handle, times(0), values(0), 3) ,
that, in the array "values",:
values(0) = 624, values(1) = 3820, etc,
values(11) = 624, values(12) = 3820, etc.

Will this be consistent behaviour of the routine UsbAdc11GetTimesAndValues? If so, we can perhaps handle it via programming. But do Pico see this as normal?

Here are results displayed in the spreadsheet if I run the macro in usbadc11_156.xls 21/8/06 as received from yourselves (except that I changed the Function to read: adc_to_mv = value / 4095 * 2500). That macro has only 2 channels specified, Ch's 1 and 2. Again the reading from the last Ch specified is in col B and that from the first
Ch is in col C:
Time Ch 1 Ch 2
us mV mV
0 2137 2332
200 2137 2330

If I change to specifying ch's 11 and 1, I get:
Time Ch 11 Ch 1
us mV mV
0 2332 379
200 2332 381

Thanks again for the efforts thus far. I will be grateful to learn your reaction to above.
Attachments
usbadc11_156d.xls
(54.5 KiB) Downloaded 826 times

Michael
Advanced User
Advanced User
Posts: 656
Joined: Thu Jul 07, 2005 12:41 pm
Location: St Neots, Cambridgeshire

Post by Michael »

Hello,

Thank you for the full detail.

This looks like a VB code problem. Perhaps you should look at the way the macro works through each line of data. Also, each channel will have a unique place in the array.

I need to spend some time over this. Also, the array will always start at zero. So the channel order will start 0-10, then 11-21, 22-32 etc.

Best regards,
Michael
Michael - Tech Support
Pico Technology
Web Support Forum

inschris
Active User
Active User
Posts: 13
Joined: Sun Oct 16, 2005 2:28 pm
Location: Capetown

using USB ADC11/10 to get data into Excel via VBA (or DDE)

Post by inschris »

Thanks, Michael, for yr response posted 5/9/06. I appreciate that time may be required.

In my posting 27/8/06, I mentioned Excel SR-2. This was incomplete; should have read Excel 97 SR-2.

Here are some additional observations from using .xls files based on usbadc11_156.xls 21/8/06 attached to yr post 21/8/06, which I hope may help towards clarification:

After changing to reading only 1 channel (Ch1), the line
nValues = UsbAdc11GetTimesAndValues(etc...)
gave 3 readings in the array times(), but nValues = 5 (which also appeared in the array Values()), not 3, tho the readings were all correctly from ch1. Adjusted the code to avoid out of range error during writing to cells by introducing nSamples. So display in Excel was OK. (usbadc11_156f.xls, attached)

Still using usbadc11_156f.xls:
Then tried all 11 ch's, w nSamples = 1, usforblock = 500. In GetTimesAndValues, left no_of_values at 3. nValues was 1 (!) and there were 12 values in array values(), again displaced by 1 (as duly reflected in the 1-row display in Excel)

Then tried all 11 ch's, w nSamples = 3, usforblock = 500. sampleInterval was 1658. In GetTimesAndValues, set no_of_values at 11. nValues was 3 (!) and there were 34 values in the array values() (values(0) to (33) inclusive), again displaced by 1 (as duly reflected in the 3-row display in Excel). Ie, after line
nValues = UsbAdc11GetTimesAndValues(etc...) ,
the 34 values in the array values() print as follows, in the immediate window:
values(0) 624
values(1) 3820
values(2) 3500
values(3) 3180
values(4) 2864
values(5) 2548
values(6) 2228
values(7) 1908
values(8) 1584
values(9) 1268
values(10) 944
values(11) 624

values(12) 3820
values(13) to (21) closely replicate (2) to (10)
values(22) 624

values(23) 3816
values(24) to (32) closely replicate (2) to (10)
values(33) 624

So it appears that the line: nValues = UsbAdc11GetTimesAndValues(etc...)
- results in 34 values being written into the array values(), instead of 33, ie:
- one extra value is written in. Is this at the beginning, from Channel 11, possibly from a previous set of 11 readings?

I note that times(0) is always 0.

These observations occur also when running under Win2000, SP4.

Regarding channel numbering, just to see, I changed to:
channels(0) = 0
channels(1) = 1
....
channels(10) = 10
Then UsbAdc11Ready doesn't return true, looping persists.
Attachments
usbadc11_156f.xls
(55 KiB) Downloaded 815 times

Post Reply