Example of a front panel control spreadsheet
1 / 12

Example of a “Front Panel” (Control) Spreadsheet - PowerPoint PPT Presentation

  • Uploaded on

Example of a “Front Panel” (Control) Spreadsheet. Bob Angstadt May 4, 2004. Introduction. What’s interesting about hv6_nt617.xls? Example of controlling a fairly complex piece of equipment with DAC, A/D’s and a multiplexers via the “Bit3” & now “SBS”.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Example of a “Front Panel” (Control) Spreadsheet' - stash

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript


  • What’s interesting about hv6_nt617.xls? Example of controlling a fairly complex piece of equipment with DAC, A/D’s and a multiplexers via the “Bit3” & now “SBS”.

    • Example of putting (external) functions in a cell. (they talk to VME!) and controlling when they are updated.

    • Saves lines of codes by using built in Excel functions where possible. (Vlookup()).

      • It is modeled after and based on a DOS based Turbo Pascal program that was originally ~25,000 lines of code and took ~4 man-years to develop. Excluding the VBA library’s there is only ~ 1,000 lines of VBA code behind the buttons. It was done in a few months. Admittedly it does not have all the functionality of the original program but the basic control is there: probably equivalent to ~ 6,000 lines of the original program and ~ 2 man years.

    • Introduces making ones own user defined types (object-like precursor.)

1 forcing excel to re calculate an external function
1. Forcing Excel to Re-Calculate an External Function

  • One problem with putting external functions directly in a cell is that Excel doesn’t know VB_readi(VME_address) is an external device that needs to be executed when Excel recalculates for the screen to update! A fix/workaround is:

    • Design the worksheet so that all of the VME addresses used in the external function calls all are based on cell reference: in this case its in Cell C7.

    • Next turn off recalculation via a VBA call to Sub “allAutoOff” from the “iomod1e” VBA module found in many worksheets. Same as:

      • Macro Recording of “Tools”, “Options”, “Calculation” & then “Manual”.

    • Next read the value of the base cell, C7, and store it in a VBA variable.

    • Poke in a garbage number into C7.

      • Excell sees and records this cell as having changed.

    • Poke in the real VME address.

    • Turn on calculation again with Sub “allAutoOn” in “iomod1e” module.

      • Macro Recording of “Tools”, “Options”, “Calculation” & then “Automatic”.

    • A Logic analyzer would then show that VME bus operations taking places for all of the external functions being updated!

    • This has already been coded for you as “Sub readvme” in module “iomod1e”.

How do we get to the bit 3 anyway
How do we get to the Bit 3 anyway?

  • One can call any function in any (external) DLL (Dynamic Link Library) including any “Windows” function and/or “the Kernel”!

    “Declare Sub Sleep Lib "Kernel32.DLL" (ByVal dwMillisecconds As Long)”

    • Dynamic as opposed to static linking right after compile time with a “linker” and/or link step. This is when external addresses in the (binary) “obj” file are resolved. Address resolution may also be done at “Run time”.

    • Any function may be put into a DLL including DLL’s that talk to hardware referred to as “driver dll’s”.

    • There is a matrix of these I’ve written with a “common” interface (function calls) depending on Bit3 model and Windows Operating System.

      • http://d0server1.fnal.gov/users/angstadt/www/d0notes/2589/convertb3.htm

  • This is done by telling VBA the function name and the file specification of the DLL it is in with VBA code:

    “ Declare Function initio Lib "bntdv617.dll" (iaddMod As Integer, _

    lB3baseAdd As Long) As Integer”

  • Here’s a “Kernel” call:

    “Declare Sub Sleep Lib "Kernel32.DLL" (ByVal dwMillisecconds As Long)”

A digression about argument passing
A Digression about argument passing:

  • The Default method VBA uses to pass arguments is “by reference” as opposed to “by copy”.

    • (Can still pass arguments “by copy” by preceding the argument with the “ByVal” qualifier.)

    • All though the same initio() function is in all the libraries it acts slightly differently depending on the Bit3 model.

      • So for the model 403/406 (ISA bus) lB3baseAdd must be set to match the jumpers on the Bit3 at 0xd0000 before making the call or the initio will always fail!

      • For the model 6xx (616,617,618,620) they are on the PCI bus and lB3baseAdd is returned as the software interrogates the PCI bus and finds out at run time where on the bus the card is!

  • So best practice is to use variables for all the arguments and not a constant or a literal number as under the hood so to speak VBA is really using pointers by default. (Same as FORTRAN, opposite of C default.) Bit3 and almost all of my DLL entry points are default VBA calling convention of using ((implicit) pointer) calls “by reference” .

2 using the built in functions vlookup
2: Using the built in Functions. “VLOOKUP()”

  • Finding and using this function saved me from gobs and gobs of complicated code. The problem is that there are 16 (4 bits worth) of different types of pods returned in cells F10 through F17. All have different Voltage and Current scale factors contained off to the side in V4 through AB30.

    • This worksheet is a “port” from Turbo Pascal to VBA and Excel.

    • So using this canned function saved me from having to re-implement a rather complicated structure and translate it into VBA and Excel.

      . Saved me time to work on the next great project!

3 user defined structures
3. User Defined Structures

  • VBA supports user defined structures.

    • Can be more work to implement then the typical way of keeping track of a card’s VME addresses and registers.

    • But once it is implemented you’re glad you have it as it makes for a more robust product and fewer programmer headaches.

    • Intro to object design.

  • But first what does VBA look like without a user defined structure!

E g with out using a structure to hold addresses
E.g., With OUT using a structure to hold Addresses

  • This sheet has an example of this. Most sheets have code like:

    “Sub go53mhzOsc()

    Dim lAdd As Long

    Dim iIs1EqualTrueIfSuccessElse0EqualFalse As Integer

    Dim iPlace As Integer

    iPlace = VB_clrlatcherri' clear the latching bit3 status error flg (iplace should always =0 !)

    lAdd = lVRBCBASEADD + 32782‘&H800E 'vba sign extension bug bites...

    iIs1EqualTrueIfSuccessElse0EqualFalse = VB_Writew(lAdd, 0)' select normal operation for autotest fpga

    If VB_islatcherri = 0 Then

    'MsgBox "no errors detected processing list in sub ListProc“ ‘ may not honor empty block if!


    MsgBox "one or more NODTACK during go53mhzosc"

    End If

    ' Go to IDLE NRZ in order for the Sequencer to establish the framing bit

    ' Call goIDLEnrz

    End Sub 'go53mhzosc “

User defined structure 1
User Defined Structure (1)

“Type Chantype

lHVdac As Long 'integer' { maps to address of hv dac setting }

lCurlim As Long 'integer' { maps to address of cur trip setting dac }

lComstat As Long 'word'{ status and control register location }

lPod_ID As Long 'word'{ where to read what kind of hv p.s. it is }

End Type

Type Modtype

Channels(iLASTCHAN) As Chantype' each board has an array of Chantype

lDigstat As Long'word; {xxxxxxxx xstatuss register of a/d chip }

lDigitise As Long'integer; { 16 bit val of a/d }

lSetadc As Long'word; {xchnpara mxxxxxxx mux contoller of a/d }

lMod_SN As Long'word; { 16 bit card i.d. }

End Type

Global HVmodAdds(iLASTSLOT) As Modtype'[0..5] of ^modtype ;” 1 module / sheet here!

Have to init the structures first
Have to init the structures first:

“Sub initAddType()

Dim iSlot As Integer

Dim iChan As Integer

'module stuff

Dim lModBase As Long

Const lCHANADDINC As Long = 16

'module stuff

Const lSLOTINC As Long = 256

Const lDIGITOFFSET As Long = &H80

lSlot0BaseAdd = Worksheets("hvSlot0").Cells(7, 4).Value

iBVAddMod = Worksheets("hvSlot0").Cells(2, 4).Value

lBaseAdd = lSlot0BaseAdd


'setup addresses for the slot

With HVmodAdds(iSlot)


With .Channels(iChan)

.lHVdac = getBiasSetADD(iChan, lBaseAdd)

.lCurlim = .lHVdac + 2

.lComstat = .lHVdac + 4

.lPod_ID = .lHVdac + 6

End With

Next iChan

iChan = 0

.lDigstat = getBiasSetADD(iChan, lBaseAdd) + lDIGITOFFSET

.lDigitise = .lDigstat + 2

.lSetadc = .lDigstat + 4

.lMod_SN = .lDigstat + 6

End With

lBaseAdd = lBaseAdd + lSLOTINC

Next iSlot

End Sub'initAddType”

And finally we get to use our structures
And finally we get to use our structures!

  • So this is an example of using the address structure with the Bit 3 library. Note that the call to init the structures, “call initAddType” is done by the caller before calling below else lAdd will be = 0 (VB’s default declaration value):

    Function setBiasV(iSlot As Integer, iChan As Integer, iVal As Integer) As Integer

    Dim lAdd As Long

    Dim iStat As Integer

    Dim iPlace As Integer


    lAdd =HVmodAdds(iSlot).Channels(iChan).lHVdac

    setBiasV = VB_Writei(lAdd, iVal)

    End Function

  • Pluses are it saves an addition each time and clean and logical code.

  • Downside is additional time/effort to set up the structures.

  • For modules with lots of registers it is the best way to go!


  • Hv6_nt617.xls is an example of using external Bit3 functions that go to a different computer and having them update by pressing a button. When they are updated can be controlled through VBA code.

  • Saves lines of codes by using built in Excel functions where possible. The main “modHV” is ~1306 lines long. Conservative estimated savings are on the order of ~ 6 to 1 for VBA and Excel over all code solutions. Even more in terms of development time through code reuse: 2 man years verses ~ 2-4 months is HUGE!

    • (This is excluding driver (DLL) library efforts which are not unique and are in many/most sheets. Also some drivers may be supplied by vendor(s)).

  • User defined types (object-like precursor) enhance coding consistency and can make a programmer’s life easier as well as the code more robust at the expense of some up front development time.