Example of a “Front Panel” (Control) Spreadsheet

1. Example of a “Front Panel” (Control) Spreadsheet Bob Angstadt May 4, 2004

2. 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”. • 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.)

3. 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”.

4. 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)”

5. 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” .

6. 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!

7. 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!

8. 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! Else 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 “

9. 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!

10. 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 For iSlot = iFIRSTSLOT To iLASTSLOT 'setup addresses for the slot With HVmodAdds(iSlot) For iChan = iFIRSTCHAN To iLASTCHAN 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”

11. 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!

12. Conclusion • 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.