Products > Computers

Excel adding digits when logging with VBA script

(1/2) > >>

Not sure if this is the correct part of the forum.

I'm using a VBA script to control a Datron 1281A DMM, take a reading every 15min, and dump it into Excel. And it works... except Excel keeps putting numbers in that are different to what the DMM has reported on the GPIB bus (I'm using message capture). this is with 8.5 digit resolution on the DMM, but even when I set 4 digit resolution Excel still puts in the same number of digits.

Excel formatting for the cells is "general", I tried setting to number with a specific number of decimal places, and it still makes figures up, just less of them.

Read from DMM =
Logged in Excel

Not a clue why it is doing this, I'm not a programmer by any means,  someone sent me this script that they wrote for a 34401A originally, I'm trying to alter it to my purposes. ANds they aren't sure why it's doing this either.

Here's some of the code

Class Module C1281
Public Property Let Mode(Value As String)
    pMode = Value
    If pMode = "AC" Then
        Call Talkto(pGPIB, "ACV AUTO,FILT-ON,RESL4" & Chr$(10), 100)
    ElseIf pMode = "DC" Then Call Talkto(pGPIB, "DCV AUTO,RESL8", 100)
    ElseIf pMode = "FREQ" Then Call Talkto(pGPIB, " ", 100)
    ElseIf pMode = "RES" Then Call Talkto(pGPIB, "OHMS AUTO,TWR,RESL6", 100)
    ElseIf pMode = "4RES" Then Call Talkto(pGPIB, "OHMS AUTO,FWR,RESL6", 100)
    ElseIf pMode = "IDC" Then Call Talkto(pGPIB, "DCI AUTO,FILT-ON,RESL6", 100)
    ElseIf pMode = "IAC" Then Call Talkto(pGPIB, "ACI AUTO,FILT-ON,RESL6", 100)
        Call Talkto(pGPIB, "DCV AUTO,RESL8" & Chr$(10), 100)
        pMode = "DC"
    End If
End Property

Sub MeasVolts()
    Sheet = "Sheet1"
    Row = 1
    'OPC = 0
    tInput = Sheets(Sheet).Range("C1").Value
    MColumn = Sheets(Sheet).Range("C2").Value
    Rdgs = Sheets(Sheet).Range("C3").Value
    Set DVM = New C1281
    DVM.Mode = "DC"
    For Reading = 1 To Rdgs
        For tDelay = 1 To tInput
            Call Wait(10)
        Next tDelay
            'While Sheets(Sheet).Cells(1, Column).Value > 1
        While Sheets(Sheet).Range(MColumn & Row).Value <> 0
            Row = Row + 1
            'Column = Column + 1
        Sheets(Sheet).Range(MColumn & Row).Value = DVM.Level
    Next Reading

    Set DVM = Nothing
    Call CloseAll
End Sub

Command Library
Sub Readlong(Device, Command, Answer)
    'Check to see if Addresses of kit loaded
    If (addresslist(Device) < 1) Then
        Call SetAddress(Device)
    End If
    Dim Buffer As String * 30
    'MsgBox ("Command Length " & Len(Command))
    Call viWrite(addresslist(Device), Command, Len(Command), Len(Command))
    'Call viVPrintf(addresslist(Device), Command & "\n", 0) 'Chr$(10), 0)
    Call Sleep(120000)
    Call viVScanf(addresslist(Device), "%t", Buffer)
    Buffer = Replace(Buffer, Chr$(10), "") '"\n", "") Line feed
    Buffer = Replace(Buffer, Chr$(13), "") '"\l", "") Carriage return
    Buffer = Replace(Buffer, Chr$(32), "") '"\r", "") Space
    Answer = Buffer * 1
End Sub

The crucial part of the code, the one that calculates the value of Level in class C1281, is missing from your post. Also, please put code in [code] tags.

Seems like the improper handling of floating point numbers. Either somebody is using Single type instead of Double for storing values, or in some way messes up conversion from DMM’s raw data.

Note that “additional digits” themselves are not the problem. Neither is the value being slightly different from what you expect. Floating point numbers represent values with limited precision, which aligns well with how measurements work. The problem here is that they are way off — below what one would expect from Excel’s storage format (64-bit IEEE-754).

Thanks for the explanation, I'll have another look at code and see if I can find the missing parts. And I'll have a look into single type and double type, see if I can see which is in use here.

I could attached the

“The” was not attached. ;) ;)


[0] Message Index

[#] Next page

There was an error while thanking
Go to full version
Powered by SMFPacks Advanced Attachments Uploader Mod