How to Use VBA & Excel with the Bit3 to VME

How to Use VBA & Excel with the Bit3 to VME R. Angstadt March, 5 2004

Why VBA? Why not VB? C?Great Glue Language: • VBA=Visual Basic (for) Applications. VB=Visual Basic is a standalone product that must be purchased separately. (“VB(A)” indicates features common to both!) VBA comes with Excel so there is no extra cost. If you are running some version of windows with Office installed then it is on your machine now! VB(A) allows reuse of the million(s) of lines of C code that Excel is written in to be reused. It provides an easy way to re-use code. No building of custom forms are required because it is so easy to use a Worksheet as a pre-defined form it’s usually not worth the trouble to make a custom one! a. VBA knows about and can access all of the functions in Excel either natively or using the function “Application.ExecuteExcel4Macro (“an_Excel_Function_Here!”)” VBA runs in Excel’s context/scope. VBA can access ~99.9% of all the Excel menus and any objects, methods or functionality they perform. b. VB(A) can call almost any function of any DLL on your computer including Kernel calls, anything you write or someone else writes in other languages such as C and/or inline assembler. The DLL it calls into could also be a “driver” DLL’s that goes to hardware! VBA and Excel are engineered to be extensible: DDE, OLE, COM & DCOM. Microsoft has published book on extending it. (“Excel Developers Kits” (various years) Book and disk(s) or CD.) c. Spreadsheets analyze stock market quotes in real time and the original article Marvin read in EE times involved operating a Nuclear Reactor!

2. VBA & Excel Enables Rapid code prototyping/development: • Saves lines of code! Engineered to be simpler than C. Savings can be as much as ~ 40 lines of C to one VB line! A simple peek, poke real windows program all in C takes > 1k lines excluding the Bit3 libraries/drivers. A Bit3 list processor can be done in < 100 lines excluding the same Bit3 libraries/drivers. Typical realized average savings in lines of code has been on the order of a factor of ~10. • Saves person power: The HV front panel application that took 6 to 8K lines of Pascal and ~2-3 PERSON YEARS was done in ~ 1,000 lines in ~ 4 months. (In terms of lines saved for this project at least a factor of 6 (conservatively) or more in savings.) 3. Originally suggested by M. Johnson as an easier way to move from DOS (Turbo Pascal) to Windows environment. (We actually tried something similar cerca ’86 with Lotus 123 macros but it was not modular and hard to maintain and the screen flashed annoyingly.) VBA and Excel have overcome these deficiencies.

FYI only: Before GetOpenFileName in C can be called it takes 44 lines to fill the structure & call! void PopFileInitialize (HWND hwnd) { static char szFilter[] = "Data Files (*.DAT)\0*.dat\0" \ "Paint Files (*.BMP)\0*.bmp\0" \ "Text Files (*.TXT)\0*.txt\0" \ "All Files (*.*)\0*.*\0\0" ; ofn.lStructSize = sizeof (OPENFILENAME) ; ofn.hwndOwner = hwnd ; ofn.hInstance = NULL ; ofn.lpstrFilter = szFilter ; ofn.lpstrCustomFilter = NULL ; ofn.nMaxCustFilter = 0 ; ofn.nFilterIndex = 0 ; ofn.lpstrFile = NULL ; // Set in Open and Close functions ofn.nMaxFile = _MAX_PATH ; ofn.lpstrFileTitle = NULL ; // Set in Open and Close functions ofn.nMaxFileTitle = _MAX_FNAME + _MAX_EXT ; ofn.lpstrInitialDir = NULL ; ofn.lpstrTitle = NULL ; ofn.Flags = 0 ; // Set in Open and Close functions ofn.nFileOffset = 0 ; ofn.nFileExtension = 0 ; ofn.lpstrDefExt = "dat" ; ofn.lCustData = 0L ; ofn.lpfnHook = NULL ; ofn.lpTemplateName = NULL ; } BOOL PopFileOpenDlg (HWND hwnd, PSTR pstrFileName, PSTR pstrTitleName) { ofn.hwndOwner = hwnd ; ofn.lpstrFile = pstrFileName ; ofn.lpstrFileTitle = pstrTitleName ; ofn.lpstrDefExt = "dat" ; ofn.Flags = OFN_HIDEREADONLY | OFN_CREATEPROMPT ; return GetOpenFileName (&ofn) ; } // ..... here's the call in some other routine someplace else PopFileInitialize (hWnd) ; if (PopFileOpenDlg (hWnd, szFileName, szTitleName)) { .... open the file and read something from it.

File Dialoque Boxes in VBA takes a few lines. • Information can always be read or written to a file. Furthermore it can be done using the same “file dialogue” box Excel uses with just a few lines of VBA code. Here the whole get a file to read routine takes 14 lines. ~ 2 lines for arguments & 1 to make the call is ~3 lines max. Sub getFileNameVRB() Dim szTitle As String Dim szFilter As String Dim szFileName As String Dim szOKwasPressedNoCancel As String ' Select a file to print, use the standard excel get a file dialogue box szTitle = "S record .hex file to process" szFilter = "S record .hex Files (*.hex), *.hex" szOKwasPressedNoCancel=Application.GetOpenFilename(FileFilter:=szFilter,Title:=szTitle) If szOKwasPressedNoCancel <> "False" Then szFileName = szOKwasPressedNoCancel Cells(12, 4).Value = szFileName ‘could open it here and do something! End If End Sub 'getFileNameVRB “ • Here’s the VBA help notes dialogue box for obtaining a file to write (save) to taking ~1 lines (plus 3 for error handling!). “ fileSaveName = Application.GetSaveAsFilename( _ fileFilter:="Text Files (*.txt), *.txt") If fileSaveName <> False Then MsgBox "Save as " & fileSaveName End If ” • See the VBA help notes for use of “Open”, “Close”, “Print”, “Write” ect.

VBA has been Modernized! • Line numbers obsolete (though still supported!) It’s now a modern language with block structure concepts like Pascal and C including Case statements. Has modules (ala Modulo), arguments passed by reference by default (like Fortran so arrays can be passed like many are used to. ) • Has a very nice IDE (Integrated Development Environment (ala Borland Turbo Pascal) with editor and real debugger, and context sensitive help. • Has Short Comings but is Quite Useable. Any shortcomings can usually be worked around and/or overcome with some C code in a DLL! No show stoppers so far! • It has maintained it’s backward compatibility very well. (Old code has not been broken. Of course macro recording on say Version 10.x and then running on Version 9.x may not work! Have to record on Version 9.x and run on Version 10.x!) (Even includes code from some early DOS versions as per Craig Symonds in ’96 Group Programmer Manager for the VB group (VB, VBA, and scripting for last 5 years from ’96. (pg xxx of VBA Developer’s Handbook, Sybex ’97 by K. Getz & M. Gilbert.) I can verify this as one time I used an old function IBM DOS function from an IBM Basic manual that I had laying around but was not Microsoft’s help at the time. When I typed it in it ran! (Integer is still 16 bits even after they ported it from a 16 bit version to a 32 bit version!) • Will try to not speak too much about operating systems as it’s almost a moot point here but it will be unavoidable at some point so I would like to explain the following right now in the beginning. • “Win 9x” shall mean Windows 95, (OSR2), Windows 98 (1 and 2) through “ME” (“Millennium Edition”) because from the stand point of most driver development including my Bit 3 drivers they are the same. (Same driver for all of them!) • Likewise, “NT” shall also include Win2k, and XP in a generic sense as they all use basically the same driver model which is of course is a completely different driver model from #1 above. [Win 9x and NT were developed by two completely different programmer “teams” with different design goals and emphasis even thought they are from the same company so they are really pretty different operating systems. Though they share some components, their code base is different.] Go to “File Explorer”, “Help”, “About” in each and you will see NT is “Release 4” (Service pack 6) Win2k is “Release 5.0” (Service pack 3) and XP is (only) “Release 5.1”. So except for marketing and hype they are all still basically NT. The same goes for driver model. The same Bit3 driver of mine almost works on all of them except for XP which requires a tweak and has it’s own version. (Haven’t tried the XP tweak on Win2k and/or XP.) Thus, NT shall mean any and all of NT, Win2k, or XP unless XP or Win2k are specifically mentioned.

Our First VBA program! • The key combo Alt-F11 brings up the VBA IDE. (If not then “Tools”, ”Macro”, “Visual Basic Editor”) brings it up. (Many ways to get back to Excel including Alt-F11. • On the VB IDE click on “Insert” and then “Module” and “Module1” comes up in the property box. In the largest white area type in “ Sub hello() Cells(1, 1).Value = "hello world" End Sub “ • Go back to Excel (Alt-F11) and run it. Various ways but Alt-F8 works! And then run! “hello world” appears in upper left hand corner cell “A1”. Not so exciting by itself. But it could be put anyplace on the screen by changing the 1,1 to any row numbers of 1 to 65536 and the column number from 1 to 255. Also note that “cells” ends in an “s” (plural.) This is intentional and signals it is an array and/or “collection”. For arrays and/or collections within Excel this is amazingly consistent! The “.” is an separator for objects and/or methods. Thus “object.sub_object_child1…sub_object_n.final_object_or_method” is typical syntax. More and better concrete examples to follow.

CountWrite: our 2nd Subroutine! • Slightly more exciting is: “ Sub count2Ten() Dim lRow As Long For lRow = 1 To 10 Cells(lRow, 1).Value = lRow Next lRow End Sub “ • Although it is not required to declare everything, it is a good idea to do so. Putting “Option Explicit” at the top of the module requires that you declare everything. • Another thing that is good to get in the habit of doing is after you make an edit in the VBA IDE is to go to the “Debug” menu pick and then “Compile”. One good reason to do this is that if you are just making a small change it may not be seen by VBA so the “Compile” will be grayed out! For example if you go to the 10 and type in 5 and then go run the macro chances are it will do the loop 10 times. To remedy this one must move the cursor off the line and/or go to the end of a line and hit the [Enter] key (it adds a blank line, you can delete it if you want!). But the point of all this is to make some changes in the code that it will see so that the “Compile” will not be grayed out. While some interpretation is done while you type, “Compiling” does a lot more including (argument) type checking of the whole project. It’s just a good idea and may save you having to come back and add a “Dim” statement as well as possibly helping to keep the Worksheet file size minimal. Also when the button is pushed there will be lower latency the first time as it won’t have to automatically compile it first: (assuming it was a big enough change that it saw…) it will already have been “compiled”!

Our Third: Puts Formulas in Sheet! “ Sub Circumference() 'puts formulas in the cells: sheet can update immedietly when user types in a new radius! Dim lAry(11) As Long Dim lRow As Long Dim lIndex As Long 'put up some headers Cells(1, 3).Value = "Radius" Cells(1, 5).Value = "Circum Formulas!" 'read output of sub count2Ten() For lRow = 1 To 10 lAry(lRow) = Cells(lRow, 1).Value Next lRow 'make a little radius, circumference table lIndex = 1 For lRow = 2 To 11 Cells(lRow, 3).Value = lAry(lIndex) Cells(lRow, 5).Formula = "=2 * Pi() * C" & Format(lRow) lIndex = lIndex + 1 Next lRow End Sub 'Circumference “

Circumference2: Just the Values! “ Sub Circumference2() ' another way: all from code: the macro must be re-run if any radi are changed! ignores user input! Dim lAry(11) As Long Dim lRow As Long Dim lIndex As Long Dim dPi As Double 'put up some headers Cells(1, 3).Value = "Radius" Cells(1, 7).Value = "Circum Values Only" 'read output of sub count2Ten() For lRow = 1 To 10 lAry(lRow) = Cells(lRow, 1).Value Next lRow ‘way cool: calling an excel function from within VBA! dPi = Application.ExecuteExcel4Macro("Pi()") 'make a little radius, circumference table lIndex = 1 For lRow = 2 To 11 Cells(lRow, 3).Value = lAry(lIndex) Cells(lRow, 7).Value = 2# * dPi * CDbl(lAry(lIndex)) lIndex = lIndex + 1 Next lRow End Sub 'Circumference2 “

Circumference 3: Using Functions “ Sub Circumference3() ' uses functions for better code reuse Dim dAry(11) As Double Dim lRow As Long Dim lIndex As Long Dim dPi As Double 'put up some headers Cells(1, 3).Value = "Radius" Cells(1, 9).Value = "Circum Values Using Function calls in code" 'read output of sub count2Ten() For lRow = 1 To 10 dAry(lRow) = Cells(lRow, 1).Value Next lRow 'make a little radius, circumference table dPi = getPi() lIndex = 1 For lRow = 2 To 11 Cells(lRow, 3).Value = dAry(lIndex) Cells(lRow, 9).Value = doCircum(dAry(lIndex), dPi) lIndex = lIndex + 1 Next lRow End Sub“

User Written Functions called by Circumference3! (can call from Excel!) “ Function getPi() As Double ' this is a way cool thing: any function in excel you can call from vba! ' pi() is an excel function returning pi to 15 places; it’s not intrinsic to vba! getPi = Application.ExecuteExcel4Macro("Pi()") End Function Function doCircum(dRadius As Double, dPi As Double) As Double ' you can call this from another vba sub or function or right from excel! ' if excel doesn't have a function you need you can write your own! ' even making use of the functions built into excel. doCircum = 2# * dRadius * dPi End Function “

Icing on the (GUI) cake: Buttons • Lets Make a button and assign it to a macro: from Excel go to “View”, “Toolbars” and make sure “Forms” is clicked. Then click on the fourth object on the “Forms” toolbar which is a “Button”. Move the mouse to the Worksheet and press and hold down the left mouse button and drag it somewhere and release it. • You’ve made “Button 1” It can be attached directly to any subroutine (without arguments) that you write. Right click on it and attach it to one of the macros that we wrote earlier, say “Circumference”, change the text on the button, “Button 1”, to “Circumference”. Unselect the button by clicking anywhere on the sheet. • Now any time you left click on the button the “Circumference” macro will be run. It took 0 code to make the button and assign it to the macro! Alternatively you could have done it all from code: You can change it’s name or re-assign which macro it is run with at any time. It’s all built into Excel. • Macros can be attached to many objects: “TextBoxes”, Pictures, (.gifs) etc. • Note: there are 2 buttons, (2 kinds of objects). Global and restricted (or local scope). (historically, old and new). I prefer the Global objects on the Forms: button because I believe they are more useful: one can attach them to generalized code that will work on the “Active” sheet. The other “Control Toolbox” buttons/objects are restricted and work only on the sheet they are attached to. They can’t be copied to another sheet. Further pain is that they are edited in “Design mode”. (Sheet cannot be saved when in “Design Mode”) They are just one pain after another and I believe they should be avoided (like the plague.... Button from hell, etc. You’ve been warned!)

Subroutines and Functions: Which when? • Though Functions can return something in a cell when they are placed in a cell they generally can NOT in any way act on the screen: e.g. can’t use Cells(row,col).Value in a function to write to the sheet. (Must use a “Sub”) Also: Function try2callExcel4(szS As String) 'if szs="zoom(75)" it doesn't zoom the screen because a function can NOT act on the screen Application.ExecuteExcel4Macro szS End Function Sub zoom() ' however this changes the screen size of the active sheet! Application.ExecuteExcel4Macro "ZOOM(75)" End Sub If something doesn’t work in a function try a sub! • Functions can NOT be hooked to a button or other object. (Must use a “Sub”). (Also if a subroutine has arguments it cannot be attached to a button directly. So to test a generalized module (with arguments) must write a Test module to call the generalized module. Sometimes referred to as a “shell” sub or just “shell”.) • But functions can be put in a cell and return a value! (“Sub” can NOT.)

Real Power, Importing External Functions in DLL’s! • VB(A) has the ability to “Register” any external function in any DLL (Dynamic Link Library) on your computer. This DLL could ultimately be connected through a driver to hardware! This can be as general or as specific as the DLL and driver it connect to. [Don’t get thrown by DLL. It just means that the address of an external (object, binary or library) function is found, resolved, loaded, and executed (run) dynamically when called by a Click of a button somewhere or by some piece of code doing something! This is in contrast to the older more typical Static Library that is linked to at Code Compile and Link time. Win9x and NT are both almost entirely based on “modules” of (typically C) code which can call into each other back and forth by the mechanism(s) of dynamic loading and linking (DLL)! What it allows is just a code module (DLL) to be recompiled and/or replaced (as long as no entry points are deleted or no arguments of functions have been reduced. Their can be more functions but not less.) It also saves having to “recompile the (whole) kernel”. One just has to recompile that DLL which may or may not be part of the Kernel.] • For our first example of “registering” and calling an external DLL we’ll go right for the maximum gusto! Remember when Basics’ of yore included “peek” and “poke” (ing) to hardware? This is now once again made possible on NT if Dale Robert’s clever driver giveio.sys is installed. (Win 9x needs no driver, just a similar DLL!) (For more about this see DDJ May ’96. “Port I/O under Windows and NT” Dale’s driver also saves a few hundred clocks of NT CPU time each time a “peek” or “poke” is made so that NT is now as fast as Win9x!) The old (and new) Peek() and Poke() routines allow one to get out on both the ISA and PCI buses via the motherboard’s chipset(s) to physical address 0 to 65535 in I/O space. On Intel ’86 (all my stuff is only for Wintel platform: no other CPU is supported and only 1 CPU at that!) there is actually a physical CPU pin labeled “I/O”! When this pin (bit) is high then the address on the bus is to I/O. When not high it’s a normal (non-I/O) address. (Basically a 1 bit address modifier instead of VME’s 6 bit address modifier.) If NT is like eating at McDonald’s and Win98 is like eating at Burger King then this driver allows us to eat at McDonalds and “have it (our) way!” With this one can be destructive and corrupt your hard drive or you can be constructive and get to the Parallel Port directly or talk directly to CAMAC via an ISA bus “DSP” card board set written entirely in VBA? (Used in the village and else where.). (The NT Bit3 DLL is as fast as it is partly due to this driver.) The DLL is on “d0server4\users\angstadt\ntuitl\ntio\debug\ntio.dll” Giveio.sys install instructions are on the web: http://d0server1.fnal.gov/users/angstadt/www/b3/b3_61x.htm

Real Power, Importing External Functions in DLL’s! (2) • [There is a similar DLL and driver for normal non-I/O memory as well “d0server4\users\angstadt\ntuitl\ntphsad\debug\ntphsad.dll so with these 2 drivers and DLL one can get to the hardware of their machine on a Virtual Operating System. Drivers may be written entirely in VBA!] • Peeking probably won’t hurt your machine too much but (RANDOM) Poking is STRONGLY discouraged! • So here it finally is the VBA code “modNT_IO”. Copy and paste it in to your spreadsheet or use d0server4\users\angstadt\ntutil\ntio.xls once giveio.sys is installed. Lets be careful out there! I/O address 0x200 is “nominally” safe as it is “nominally” the game port. However your machine may be different and/or not have one. Reading 0xff back is essentially “not there”, not initied, or in VME speak, “NO DTACK”. I/O address 0x378 is nominally LPT1 but this depends on the BIOS and “Ports”, “Resources” in the “Device Manager” of the “Control Panel”. Your mileage will definitely vary. Finally this is the VB(A) code: “ Declare Function pokeport Lib "ntio.DLL" (iAdd As Integer, iVal As Integer) As Integer Declare Function peekport Lib "ntio.DLL" (iAdd As Integer) As Integer Function VB_pokeIO(iAdd As Integer, iVal As Integer) As Integer Dim i As Integer i = pokeport(iAdd, iVal) VB_pokeIO = i End Function Function VB_peekIO(iAdd As Integer) As Integer Dim i As Integer i = peekport(iAdd) VB_peekIO = i End Function “

The C code the VBC calls: • Part of ntio.c (so simple I didn’t make a ntio.h file. (Shame on me.) Normally the *.h file is what one looks at to “register” (import) C DLL’s into VB(A) as above.) If I did have a ntio.h file it would have about 2 lines in it: DLLEXPORT short int WINAPI pokeport(long *plportadd, long *plval); DLLEXPORT short int FAR PASCAL peekport(long *plportadd); • The *.h file is supposed to hold the function “declarations” that are exported so other modules can include the *.h file so that they may (static) link to it at compile time. I (unfortunately must have been in a hurry) and skipped making a *.h file. I evidently only cared about the VB(A) dynamic part. Below is the relevant part of ntio.c: “… DLLEXPORT short int WINAPI pokeport(long *plportadd, long *plval) //writes ival to the p.c. port address specified by plportadd. { int ilocalval; unsigned short wadd; ilocalval=(int) *plval; wadd=(unsigned short) *plportadd & 0xffff; return _outp(wadd,ilocalval); }; //{---------------------------------------------------------------------------} DLLEXPORT short int FAR PASCAL peekport(long *plportadd) //reads a value, itemp, from the p.c. port address specified by plportadd. { unsigned short wtemp; wtemp=(unsigned short) *plportadd; return _inp(wtemp); }; …“

FYI: MOST OF THE WHOLE NTIO.C DLL SOURCE clipped some comments:ALSO IS AN EXAMPLE OT WHATEXCEL SAVES YOU FROM! (FYI only. No quiz on this.) “…. # include <windows.h> //# include <c:\cpp\xlcall.h> nothing from this really used # include <stdio.h> # include <conio.h> // macro def for port i/o outp is in conio.h # include <dos.h> //# include <alloc.h> // nec. for malloc() # include <process.h> // nec. for exit() # include <string.h> //# include <math.h> //true defined in windows.h and other places //#define FALSE 0; //#define TRUE 1; HANDLE hDriver; /* gets called at load time...when the function is "registered". */ /* main entry point */ BOOL WINAPI DllMain(HINSTANCE hDLLInst, DWORD fdwReason, LPVOID lpvReserved) { BOOLEAN bret; #ifdef _DEBUG OutputDebugString("\n\rIn DLLEntryPoint: DllMain\r\n"); #endif switch (fdwReason) { case DLL_PROCESS_ATTACH: // The DLL is being loaded for the first time by a given process. // Perform per-process initialization here. If the initialization // is successful, return TRUE; if unsuccessful, return FALSE. //5/17/99 try to open up giveio.sys // give this process access to any i/o space transparently! hDriver = CreateFile("\\\\.\\giveio", GENERIC_READ, 0, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL); if(hDriver == INVALID_HANDLE_VALUE) {

//printf("Couldn't access giveio device\n"); bret=FALSE; } else { bret=TRUE; } //giveio modifies the i/o permission map in this task's tss // task segment selector (according to the author) so // once this is done, it's done! when the last dll user goes so // does the task and it's permission map so we don't need giveio.sys // anymore! (very very nice!) no cpu cycles are used on nt protection! CloseHandle(hDriver); break; case DLL_PROCESS_DETACH: // The DLL is being unloaded by a given process. Do any // per-process clean up here, such as undoing what was done in // DLL_PROCESS_ATTACH. The return value is ignored. // release all driverrs and resources //if (callgateflg!=0) { //if (!DeviceIoControl(hDevice, DMABUF_FUNC_FREEBUFFER, // &dmadesc, sizeof(DMA_BUFFER_DESCRIPTOR), // NULL, 0, &cbBytesReturned, NULL) // ) //{ //printf("DeviceIoControl failed, error=%d\n", GetLastError() ); // should put return false here //} } // close mapmem.sys handle (totalio.sys closed in init) CloseHandle(hDriver); bret=TRUE; break; case DLL_THREAD_ATTACH: // A thread is being created in a process that has already loaded // this DLL. Perform any per-thread initialization here. The // return value is ignored. bret=TRUE; break; case DLL_THREAD_DETACH:

// A thread is exiting cleanly in a process that has already // loaded this DLL. Perform any per-thread clean up here. The // return value is ignored. bret=TRUE; break; } return bret; } #define DLLEXPORT __declspec(dllexport) // this is the meat and potatoes //{---------------------------------------------------------------------------} DLLEXPORT short int WINAPI pokeport(long *plportadd, long *plval) //writes ival to the p.c. port address specified by iportadd. { int ilocalval; unsigned short wadd; ilocalval=(int) *plval; wadd=(unsigned short) *plportadd & 0xffff; return _outp(wadd,ilocalval); }; //{---------------------------------------------------------------------------} DLLEXPORT short int FAR PASCAL peekport(long *plportadd) //reads a value, itemp, from the p.c. port address specified by usportadd. { unsigned short wtemp; wtemp=(unsigned short) *plportadd; return _inp(wtemp); }; //{---------------------------------------------------------------------------} “

Call the Kernel from VBA! This is modTime in some sheets (VB(A)) code! “Public Declare Function timeGetTime Lib "winmm.dll" () As Long Declare Sub Sleep Lib "Kernel32.DLL" (ByVal dwMillisecconds As Long) Const lSLEEPTIME As Long = 12 'milliseconds Sub AbenchRead() Dim l As Long Dim lTime As Long Dim lPlace As Long Dim ival As Integer Const lMAX As Long = 1000 Const lAdd As Long = 33536 '"&H8300" -> a neg. num! 'MsgBox (" get ready to set your stop watch") lTime = getTimerVal For l = 1 To lMAX ival = VB_readi(lAdd) Next l lTime = getTimerVal - lTime MsgBox (" done reading " & Format(lMAX) & " times took " & Format(lTime) & " milliseconds ") End Sub Sub AbenchRead2() Dim l As Long Dim lTime As Long Dim lPlace As Long Dim ival As Integer Const lMAX As Long = 1000 Const lAdd As Long = 33536 '"&H8300" -> a neg. num! 'MsgBox (" get ready to set your stop watch") l = 1 lTime = getTimerVal Call Sleep(l) lTime = getTimerVal - lTime MsgBox (" done reading " & Format(lMAX) & " times took " & Format(lTime) & " milliseconds ") End Sub Function getTimerVal() As Long ' adapted from pg 214 of "VBA developer's handbook" by Ken Getz and M. Gilbert (sybex,97) getTimerVal = timeGetTime End Function“

Registering Bit3 DLL Functions! (some) registered functions “'have managed to unmangle the names for the 617 driver! ' Declare Function initio Lib "bntdv617.dll" (i As Integer, l As Long) As Integer ' Besides initing the bit3, setting the address modifier, it initializes all ' secondary error handling bite.dll flags to the correct state including ' the internal static variable "abortflg"=0 =false (no failures) or 1= a failure Declare Function islatcherri Lib "bntdv617.dll" () As Integer 'returns true=1=a failure if a bit3 error detected since the last time 'initio() or clrlatcherri() was called. call getlasterrps() to find last error! Declare Function clrlatcherri Lib "bntdv617.dll" () As Integer ' set static variable "abortflg" to 0= false = no failures and should always return 0. ' the "c" code is: '{ abortflg=FALSE; ' return abortflg; }; Declare Function getlasterrps Lib "bntdv617.dll" (ByVal s As String) As Integer ' tells what the last bit3 error was in english (more or less). ' this is a noteworthy example of how to return call a "c" function that ' returns a pointer to a null terminated string which VBasic has troubles with. ' the "byval" fixes the null terminated part but it still has trouble with the pointer so ' also basic will not allow the byval keyword after the function name so ' fake it out by telling basic it's an integer... ' from page 779 of Using Visual Basic for Applications (Excel Edition)by Jeff Webb. Declare Function setb32flgi Lib "bntdv617.dll" (iflg As Integer) As Integer ' set iflg=true=1 then bit3.dll will send out the high byte = full 32 bit(3) model 406 addressing ' else iflg=false=0 then bit3.dll will not send out the high byte = 24 bit(3) model 403 addressing Declare Function readvmew Lib "bntdv617.dll" (l As Long) As Integer Declare Function readvmeb Lib "bntdv617.dll" (l As Long) As Integer Declare Function writevmeb Lib "bntdv617.dll" (l As Long, i As Integer) As Integer Declare Function writevmei Lib "bntdv617.dll" (l As Long, i As Integer) As Integer Declare Function writevmeli Lib "bntdv617.dll" (lAdd As Long, lVal As Long) As Integer Declare Function setaddmoda Lib "bntdv617.dll" (i As Integer) As Integer Declare Function bootvme Lib "bntdv617.dll" () As Integer ….“

Bit3: Some Registered Functions Wrapped Function VB_InitIOi(addmod As Integer, Address As Long, model406flg As Boolean) As Integer Dim i As Integer Dim j As Integer Dim i406adapter As Integer If model406flg Then i406adapter = 1 Else i406adapter = 0 End If i = initio(addmod, Address) j = setb32flgi(i406adapter) ' constant set =1 or 0 after bit3.dll declarations above VB_InitIOi = i End Function Function VB_getlasterrs() As String Dim s As String * 255 Dim iworked As Integer ' this is more of J.W's trick s = String(255, 0) iworked = getlasterrps(ByVal s) ' this is the final part of J.W.'s trick VB_getlasterrs = s End Function Function VB_InitIOs(addmod As Integer, Address As Long, model406flg As Boolean) As String Dim i As Integer Dim s As String * 255 Dim j As Integer Dim i406adapter As Integer If model406flg Then i406adapter = 1 Else i406adapter = 0 End If i = initio(addmod, Address) s = String(255, 0) ' necessary now? s = VB_getlasterrs() ' do the trick only once in a vb routine j = setb32flgi(i406adapter) ' constant set =1 or 0 after bit3.dll declarations above VB_InitIOs = s End Function Function VB_readi(Address As Long) As Integer Dim i As Integer i = readvmew(Address) VB_readi = i End Function

Why Wrap Functions? • Initially it made things more robust. (Fewer blue screens of death when a c routine was passed the wrong type in an argument.) Allows VB(A) to better trap common programmer type (checking) errors b4 making the c call. • Can hide any tricks being used to call into the c code. (Strings are a bit tricky/weird/magic.) Keeps all the tricks in one place to prevent argument calling errors and thus aide robustness as above. • However the best reason here is that it allows one to change Libraries (DLL’s) easily. Due to differences in price and capability and historical reasons, various test stand use different Bit3 models and even busses. By wrapping whatever DLL is called in it’s own module one may isolate and minimize any differences to the rest of the Worksheet. Thus a whole worksheet developed on one test stand may be run on a completely different test stand by changing out the Bit3 module. A worksheet that is moved this way can be changed to run on a different operating system (different ways of getting to the hardware: drivers) and/or use a different Bit3 model (different busses!) in ~ 5 minutes by cutting and pasting in different VBA “modules”. (Note the similarity to a language called “modula” which was a successor to “Pascal”.) This module feature has been extremely useful for us. Specific Bit3 DLL and modules info available at: http://d0server1.fnal.gov/users/angstadt/www/d0notes/2589/convertb3.htm. In general modules are a good thing (precursor of objects…) and should be used if and where ever possible. In most cases the cost is at most a microsecond or 2 at run time depending on the hardware one is running on. • [Modules are also a way of dividing and conquering. If two people are working on the same sheet one could make their changes in one module and the 2nd in another. Periodically the new modules could be replace or updated and then “checked” to make sure they worked as intended together. If so then it could be “released”. Then the next round of changes would be made and the process repeated until the Workbook was “done”. This is good practice. If you take an existing Worksheet over and start to modify it then if possible please add new code modifications (your) own new module(s). That way if fixes/improvements/patches are made to the old ones in your sheet are developed by someone else the old modules can be easily changed and/or upgraded as necessary. This is easy to do as long as your changes are not mixed in with theirs. Then it’s all yours and you have to fix everything yourself! Modules can be a very good thing!]

Typical Bit3 Calling Sequence • It is assumed InitVME() is called outside of and sometime before a VME access (reading or writing) to the VME bus. Once it is called it should not have to be called again unless any of the following occurs: • Power is removed from the crate and turned back on. • VBA execution is halted via the “Esc” key or “Ctrl-Brk”. (Best to do this but may not be required…) • If for some reason a piece of code is run where a lot of invalid VME addresses occur. After a lot of “no DTACK” ing the Bit3 may be need to be reinitialized before valid addresses start working again. (After a big bunch something latches in the Bit3 that the quicker error clearing mechanisms don’t clear it. A total global restart is the only thing that brings it back but this nominally takes a second so it is not done automatically all the time in the driver. It usually takes seconds of invalid addresses before the Bit3 really latches like this. (On a fast machine in tight code this could theoretically be >=~300 to 400K times.) See next major section for more. • Any time a valid VME address is put on the bus and a VME module’s “DTACK” LED does not light. (Occurs rarely but it can happen. Reseating cards in the busses and/or cables may be the fix. Check VME crate power. Pull all VME modules but the arbitrator, Bit3 and target to check if of them has latched bus grant or some other VME bus signal. Check that all jumpers are correct on all boards. If not a VIPA crate and if the VME Bit3 card is not jumpered for Bus Arbitrator and not in slot 1 then Bus Grant 3 may not be making it to the VME Bit3 card. (The 1553 card does not jumper bus grant 3 across for example. (Crate configuration and jumpers may need to be changed.) • After calling InitVME() one should wait at least a second (and perhaps 2) to be safe. (The latest “2nd” version for NT has a half second delay in it at the end of it (already) for the precision timing calibration it uses.) All of the Bit3 driver DLL’s keep a (latch on a Bit3 detected error) global flag in them, iabortflg. It is reset (false) only at power up and only whenever VB_clrlatcherri() is called. VB_islatcherri() always returns 0. VB_islatcherri() value is in re-setting the flag without going through the whole init Bit3 process and not in what it returns. This flag, iabortflg gets set True ( <> 0) for any detected Bit3 VME failures. It “latches” in this state until reset via a call to InitVME() or VB_clrlatcherri() is called. (Thus any granularity of error checking is achieved.)

Typical Bit3 Calling Sequence 2 • VB_getlasterrs() returns a string that indicate what the Bit3 and driver thought the last VME error detected was. Possible values are " *VME TIMEOUT*“, " *VME BUS ERROR*“, " *VME PARITY ERR*“, " *DMA ERROR*“, "*connect giveio.sys failed* “, “ *connect mapmem.sys failed* “, "*no vme access: no buff from mapmem.sys* “. Any and all combinations of the first 3 may be seen together on a VME access and mean that for some reason “NO DTACK” came back for that address. The latter errors may occur only in the NT version of drivers and indicate that something is not working between the driver DLL and one of the drivers (*.sys binaries) it is using. They may not be running for some reason and/or installed. If "*no vme access: no buff from mapmem.sys* “ occurs it means something pretty bad happened in mapmem.sys and/or it’s connection was broken. Saving any unsaved work if desired and leave Excel entirely and come back into it. This seems to mostly happen when stopping VBA code execution via Ctrl-Break (during VBA development.) I apologize for not being able to totally make this go away entirely. Although it is an annoying nuisance it has not been a show stopper. Recently I’ve found that not putting “VB_InitIOs()” in any of the cells seems to delay the onset of this considerably. More on this later in “How Excel Recalculates: determinism (or lack of it.) ..”. Most VME errors are trapped but there are a few conditions that may not be. These rare and usually due to a botched driver install for some reason or other. Best is to call me if you are having troubles so I can come and look. • Read routines have no error status back and one must use the above mechanisms for error checking. They just return the value they read. (This allows the function to be placed directly in an Excel cell!) Write routines return 0 on detected failure and a 1 on success. There is enough their so that one could re-write the wrappers to give back any kind of calling interface that could be imagined/desired.) • If using getvmedmal() (only available for the 6xx models) then it returns it’s own error code in addition to the first mechanism described above. On return this must be checked after every DMA for a 1=true=data is valid. The user buffer is not zeroed in the interest of speed. (The caller may 0 some or all of their buffer before doing the DMA if they desire.) Other error codes are from bntdv617.h: “…after the dma is complete the data will be copied to this buffer and // the routine will return success=1 or failure= false=0 or -99 if // it could not create a 64K special dma buffer. if the return is -99 // close the program (unload the dll) and then reload it. “

Example of a Typical Bit3 Calling Sequence after a call to VB_InitIOs() or VB_InitIOi() (and crate not power cycled.) “ 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 “

A Word on Notation • In the code fragments presented so far you may have noticed odd things like an “l” (lower case L) in front of “lAdd” (Add is short for address: in this case a VME physical address.) “l” is short for long. In iPlace the “i” is short for integer. “sz” indicates a string with a zero terminating it (basically a C type of string as opposed to a VB string which probably will not have a 0 terminating it.) “b” is for Boolean. “mod” is short for module. “txt” is short for a text box. It must be stressed that this is a convention only. What really determines what type of the variable is the “Dim” statement wherever it is declared. Thus: “Dim iVal as Long” fails to follow the convention and is misleading: “i” implies a 16 bit integer but it is in reality a 32 bit “Long”. • This is a form of “Hungarian Notation” (plenty under Google for this string!) originally described by Charles Simonyi, a Hungarian with an unpronounceable last name, hence it became known as Hungarian notation. Originally this was for c code. G Reddick and Lesznynski expanded it to be the Reddick VBA or RVBA naming convention. One of the things Simonyi argued pretty strongly (about if I remember correctly) was to put the type up front followed by a capital letter signifiying when the type ends and when the variable name starts as opposed to having it at the end which was previously more common before Simonyi’s writings. • Naturally a lot of my C DLL code base is from code written before I knew of “Hungarian” or RVBA notations. It was based on suffix indicators such as “flg” for a flag, w, for (unsigned) word, “i” for integer, ect. After RVBA I begin using a mixture! Thus in the c variable iAbortflg the “i” is for (short 16 bit) integer (used as) a “flg”. I avoided a Boolean type because I wasn’t sure if VB and C would use the same number of bits. • For VB I think Hungarian notation is useful because an integer in VB has always been 16 bits and a “Long” has always been 32 bits from the 16 bit version 4 (Office XP is ~10 now.) ~ 8 years ago. For C where the code “iVal int;” can change from 16 bits to 32 bits or vice-versa with a different platform and/or compiler! (or perhaps even 64 with a third platform!) So old C code on ports like this could require tons of editing to fix completely misleading notations. If it’s a large quantity of lines then probably no one is going to go and change all the names because of the high probability of introducing too many mistakes. Any notation is just meant to be an aide to quick understanding of code. If it does not succeed at that then it might not be worth the extra work.

A Word on Notation (2) • Why so important? We’re doing mixed language programming so the arguments and return values must match or a possible blue screen of death could result. We have to pay attention to our calling arguments and make them match the declarations in the Bit3 module. A word or two about the code may help you decipher it if you feel the need to have to look at it! • Back to trying to clarify what you’re looking at. Here’s the C code for the islatcherri() function: DLLEXPORT __int16 WINAPI islatcherri(void) { return (short) iabortflg; } Another example is “getlasterrps” the “p” is short for pointer and “s” is short for string. So the whole thing spelled out is “Get the last error returning a pointer to a string.” • So for a lot of the Bit3 functions I used use my old suffix notation on the type of VME operation they will put on the bus and/or return: 8, 16 or 32 bits. It sort of works: “i” for signed 16 bits, “b” for byte=8. Very nice! • For a model 6xx that support 32 bit operations then the wrapped VB_readl(), & VB_writel() and VB_readul() are provided that wrap the DLL readvmeul() and putvmeli() actual DLL entry point. • Note that in all Bit3 functions is a misleading/confusing name ending “writevmeli()” [Another reason to just use the wrapped functions.] Here the conventions break down. I apologize for this. WriteVMEli() exported function available which the header file tries to clarify: “DLLEXPORT __int16 WINAPI writevmeli(long *pdaddress,long *lval); /* this name and the second argument of writevmeli are misleading. ** writevmeli does NOT do a 32 bit word vme bus cycle. ** it does only a 16 bit vme bus cycle. the argument is 32 bits to ** facilitate getting back a 16 bit unsigned quantity short to Visual Basic ** as VB has no short intrinsic unsigned (__int16) __int16. in no way shape or form ** does this do a 32 bits to vme. …“ A long is necessary to hold an unsigned 16 bit quantity. (More on next section.) • (The models 6xx can do 32 bit VME operations and there are a few additional calls for those libraries. It is easy to move up to a 32 bit model by cutting and pasting in new VBA code but going down is only easy if 32 bit VME operations are not needed/used in the rest of the sheet.)

A Word on Notation (3) • In one early sheet I found 2 possible wrappings of WriteVMEli which I thought might be interesting because the wrapper adds functionality to return the last error string instead of a 0 or 1 (failure, success respectively). If you need a slightly different style then there is enough here I think to morph the arguments and return values to your needs! This is from a module called io_32 in hv5_32.xls. It’s for a 40x under Win 9x, “Bit3_32.Dll” but it would work the same under the equivalent NT DLL, “BNTDV40X.dll”. Function VB_Writew(Address As Long, lvalue As Long) As Integer Dim i As Integer i = WriteVMEli(Address, lvalue) VB_Writew = i End Function Function VB_Writews(Address As Long, lvalue As Long) As String Dim i As Integer Dim s As String * 255 ‘ s is declared here with space (a buffer) for 255 characters i = WriteVMEli(Address, lvalue) s = String(255, 0) ' every character of the string is now set to 0! s = VB_getlasterrs() ' this returns the last error string from the bit3 C driver VB_Writews = s End Function • So this notation is to try to indicate that VB_writew() writes an unsigned word (returning the usual and/or default integer= 0,1 as a 16 bit integer) while VB_Writews() writes an unsigned word returning a string indicating an error or “ok!” if no error was detected. Both do this by wrapping the unsigned 16 bit word into a larger signed 32 bit word or long. Why do we have to package up an unsigned 16 bit quantity in a (signed) long of 32 bits?

Some Problems with VBA and Some Work Arounds • Unfortunately VB(A) has no intrinsic unsigned types. Everything is signed. An integer is always 16 bits = (0xffff) -32768 to +32767 (0x7fff) in VB(A) though other languages (usually) provide a 16 bit unsigned integer 0 to 65535 (0xffff) • Long is also signed (0xffffffff)-2,147,483,648 to +2,147,483,647 (0x7fffffff) Unsigned Long would be 0 to 4,294,967,295 = 0xffffffff) • Note: something helpful to remember is that for any signed quantity the largest positive value it will hold is 0x7f in the high byte (on a 2’s compliment machine, which most computers are including Wintel). • Assigning &h8000 to a long doesn’t work! One gets 0xffff8000 and not 0x8000. (sign extension problem on any hex value > 0x7fff. This has been a real pain! • Workarounds: • Promote everything to more Bits. Use a long to hold 16 bits being careful to use decimal instead of hex for values > 0x7fff. • Pass it onto the C stack where the C argument is unsigned and/or larger (more bits) as above. E.g., if you need 0 to 65535 then use a long. If > 2 billion then use a double. Etc. (can use arbitrary long integers with C and inline assembly. I have an example of using > 64 bits using the ADDC instruction using double and 2 longs so that modulo operations still work on the lower 32 bits. • Use strings! Can also pass it as a hex string and convert it to binary where necessary. • VB(A) doesn’t do true bit shifting… some fixes available in xutil.c and more elsewhere. • Conclusion/Summary: Due to it’s extensibility many/most limitations can be worked around. (One that can’t be is the programmer’s lack of imagination and/or skill!)

Concept of Lists • B4 objects there was “structured programming” with a fundamental tenet being that one function (and/or subroutine) did one thing (in as generalized a way as possible) (Just as there are objects now…) One can expand this to the concept of a list. • Make One list do one thing. Maybe you have a list for the first grocery store and a 2nd list for another due to a sale at the second but the first has better produce. Maybe another for the Hardware store, etc. Maybe after all your lists are made and organized you expedite them by “shopping”. The idea is the same, make a list of like VME read/writes, their addresses and values, and then expedite or execute that list! (Make the VME “DTACK” LED’s light!) • (FYI: There are languages built around this list concept including a now somewhat obsolete language called “Lisp” which I think now is quite dead due to it’s difficult to use of parenthesis on top of parenthesis. It wasn’t pretty but it had power for those that could keep track of and parse the parenthesis.) • Remember one of our first goals was to reduce the number of lines of code? One way is the concept of a list processor. Not new, many programs (had) have file based list processors of various sorts. The next slide is a simple 48 line VBA subroutine based on the Bit3 module that will process a VME list.

A (simple) VBA VME List Processor (48 lines) : Sub ListProc() Dim iRow As Integer Dim iMax As Integer Dim iplace As Integer Dim lAdd As Long Dim lVal As Long Dim iVal As Integer Dim iAddMod As Integer Const iROWSTART As Integer = 16 Call allAutoOff iplace = VB_clrlatcherri ' clear the latching bit3 status error flg iRow = iROWSTART - 3 iMax = Cells(iRow, 4).Value iMax = iMax + iROWSTART - 1 For iRow = iROWSTART To iMax lAdd = Cells(iRow, 4).Value iAddMod = Cells(iRow, 11).Value If iAddMod <> 0 Then ' if not 0 then send a new add mod iplace = VB_setaddmoda(iAddMod) End If If Cells(iRow, 1).Value = 1 Then ' do byte If Cells(iRow, 2).Value = 1 Then ' write iVal = Cells(iRow, 9).Value iplace = VB_writeb(lAdd, iVal) Else ' read iVal = VB_readb(lAdd) Cells(iRow, 5).Value = iVal End If Else 'do word If Cells(iRow, 2).Value = 1 Then ' write lVal = Cells(iRow, 9).Value iplace = VB_Writew(lAdd, lVal) Else ' read lVal = VB_readw(lAdd) Cells(iRow, 5).Value = lVal End If End If 'do error handling now Cells(iRow, 12).Value = VB_getlasterrs Next iRow Call allAutoOn If VB_islatcherri = 0 Then 'MsgBox "Should be in IDLE mode = 00" Else MsgBox "one or more NODTACK during ListProc" End If End Sub 'ListProc

List of Lists (of Lists!) • If a Workbook had several Worksheets in it with each worksheet having one list then they could be chained together for the cost of ~ 2 lines of VBA code per link. The following is a somewhat artificial example to try to make things clearer: “ sub doAList Sheets(“InitBoard1").Select call ListProc Sheets(“InitBoard2”).select call ListProc Sheets(“DoSomething1”).select call ListProc ….. ‘until all of the sheets are exhausted ‘could make branching statements using if and case based on values coming back in certain ‘cells. ‘ could also do one list multiple times ect. ‘ Can call other sheets and load them: Workbooks.Open FileName:= "C:\xls\xls_CTS\\List617_2.xls" Sheets(“Sheet VME1”).select call ListProc ‘mix it up and call another kind of list proc: 1553 Sheets(“Sheet 1553_1”).select call ListProc1553 • So with this scheme and/or others like it one can do (almost) programmingless programming. • Has been done with Fermi VME based 1553 module, Ballard ISA 1553 controller, and Ballard 1553 PCMCIA spy card. Could be done with parallel port. I believe Neil Wilcer has done it with the serial port. • Has been done with CAMAC: there is a DSP/ISA CAMAC list proc in Excel! • (Could probably be done with CAMBUS if inclined/desired.)

List of Lists (of Lists!) 2 • Any list processor can be made “Quiet”, Silent, or Invisible with “Application.Screenupdating=False” (It will also run faster!) • Just Remember to turn it on with “Application.Screenupdating=True” when you are done! Else the worksheet will no longer paint the screen again until it is reopened! (Not widely used but it is available.) • A few sheets have a quiet or deferred error handler. So they are “quiet” and don’t check for errors until all the lists have been completed. Not widely used but is available and/or could be done if desired. • List Processing Concept is very powerful. Saves lots of programming time at some expense to run time (only on slow machines!) Most widely used with 1553 and Sequencer test where even a pass/fail test was developed with it using a procedural write- up. This technique has the advantage of the same code and Worksheets being used for debugging by the engineer and/or skilled technician. Engineer made up most of the lists. Took very little additional programmer time. No time wasted on “deadcode” because all code (and Worksheets) is still in use (whenever a sequencer does need repairing)!

Types or Purpose of WorkSheets in Use • What sheets do we have done that can be looked at? (for examples, lifting/reusing code ect.) Tons! (Megabytes! > 5 Person years!) So many I am out of quota on the servers so if you don’t see it there ask me! All kinds including but not limited to (and in no particular order): • Small 1 Crate DAQ’s: (both SASEQ and VRB,VRBC based) for both Silicon chips and AFE boards. (Way different sheets.) • FPGA reprograming: A sheet for reprogramming VRB’s (downloads new firmware to battery backed RAM that it will boot from.) Also a VRBC download worksheet. These last two use the Bit3 to load new Altera programs to the FPGA’s on the board via VME & Bit3. (Jamieson uses the parallel port as an interface to his boards as they are not VME based. He as also done some JTAG testing using the parallel port as his hardware is not VME based.) • Front Panel control and display substitue: A High Voltage Front Panel Display and Control Sheet(s). • Front Panel control and display of the DZero clock and statistical testing. (run for months/years? In Feynman on a Win 9x box!. • General Engineering Control and Exercising VME, CAMAC, & 1553 List Processors for all of the above and more including the DZero Clock (in Feynman), and Mike Utes Sequencer(s). • Confirming VRB & VRBC operation and troubleshooting including engineering, Statistical (and/or FPGA emulation!) functions. Most recent sheet with many modules in it. Still under development for CTS. (A work in progress.) But a version allowed Ted Zmuda to come up with VLPC firmware that ran the VRB & 1 VRBC > 9.1 billion times without failure. (It used VME (Block Transfer= ~ DMA! & my Bit3 driver!) at L1 rate > 20Khz and L3 rate of ~ 6 Kilohertz on Lyn’s 2Gig machine (thanks Lyn!). Used Bit3 and Parallel port for trigger and handshaking. Kept track of everything and provided support for triggering Ted’s Logic Analizer via the parallel port. (A 24 bit (16Mega) wrapping problem could be exercised and looked at quicker on the logic analyzer then it could be simulated by the Altera software.) So it could trap errors and trigger things as well as do statistics. (9.1 billion events takes ~ 18 days. 0 errors. Way to go T. Zmuda!) • 1553 Spy (Logic analyzer) Worksheet on a Laptop (Shoua’s.) Uses the Ballard driver for their 1553 PCMCIA card. Some versions have the ability to trigger a scope via the Parallel Port! • AFE characterization: M. Matulik, P. Rubinov. • Pass/Fail Production Testing: (M. Matulik. and J. Anderson)

Random Hints, (words to the wise). • Don’t use spaces in object and/or sheet names. (If nothing else it may be hard to distinguish between a space and a “_” due to some line on the lower margin.) It almost works but sometimes….. Just don’t! • VB is not case sensitive (it echoes case for clarity after you declare it.) except in side of quotes: “Sheet 1” is not the same as “sheet 1” • When is VB not block structured? When the first block of the if is commented out! May have to change the sense of the if. (This recently burned me!) • Best to be explicit in the if statement: avoid C usage of If. E.g. if (myVar) then … may not work. Best is to use if (myVar>0) or if (myVar=True) or if myVar=False) which leads us to the next thing. • Spell things out sequentially. In general don’t stack things up in (). Make a variable and feed that to the next routine and/or set of (). Allows type checking and since it gives the parser less grief it gives you fewer gray hairs as well. • For speed use “With”, “End with” blocks when dealing with many instances of the same daughter object. Supposedly it saves re-verification that the parent object exists. (Unfortunately “with”s do not nest.)

A Word About Arguments (and Pointers) • Although VB & VBA does not EXPLICITLY support “pointers” the default argument passing is “by Reference” (or basically a pointer!) This is as opposed to “by copy”. FORTRAN’s default is also “by reference” as opposed to C’s default of “by copy”. (C also support passing things “by reference” as well when a functions argument declaration(s) include the use of a “pointer” ((address) dereferencing operator) = “*”. • When passing arrays back and forth between C and VB(A) one should be aware that C starts all arrays at 0. The default for VB(A) is also 0. However in VB(A) it can be changed via either “Option base 0” or “Option base 1” at the top of a module. When passing arrays back and forth between C and VB(A) I think it very wise to explicitly put “Option Base 0” at the top of the module. One less point of confusion for the interpreter and for the author/reader/programmer. It just keeps everyone on the same page. • Generally, when matching arguments to C code in a DLL if it is not declared as a pointer (no “*”) then it typically needs the VB “byval” qualifier which tells VB it is passed as a copy. (Usually) Look in the *.h file to see how to call it or other documentation… (book or whatever.). (An exception is passing strings. For now just pretend it is in the realm of “magic”. But if you insist there is an example in the Bit3 library with VB_getlasterrs() with comments and a reference. • [There is a newer dialect of VB out called “NET” VB which I’m not talking about here. Of course you know it’s default is the opposite of VB(A) and we are in no way referring to that! This was for a Web scripting environment as a Java competitor. At the moment we are just working locally… so we are less concerned about security and/or “protection” which is why they bit the bullet and changed it all around! (VB and VBA were built to be extended!)] • Probably the most important thing to realize is that passing a variable “by reference” into say a subroutine is that if that sub you passed it into changes it, then its changed in the callers variable as well. This is good for passing arrays as in FORTRAN and C. However when overlooked it is the fast track to trouble.

A Word About Arguments, Example 1 • Though the following is a contrived example of above it illustrates the point. Say you wanted 5 columns of numbers on the sheet counting from 1 to 5. These two rountines just do the indexes. (Some other routines would fill in something else later.) For indexing one might write a sub Count2MaxTest() which calls Count2Max(): “ Sub Count2MaxTest() Dim lRow As Long Dim lCol As Long Dim lStartRow As Long Dim lMaxRow As Long lStartRow = 1 lMaxRow = 5 For lCol = 2 To 10 Step 2 ‘loop over each column Call count2Max(lCol, lStartRow, lMaxRow) Next lCol End Sub Sub count2Max(lCol As Long, lStartRow As Long, lMaxRow As Long) While lStartRow <= lMaxRow ‘loop by rows Cells(lStartRow, lCol).Value = lStartRow lStartRow = lStartRow + 1 Wend End Sub “ • Only 1 column of numbers comes out when 5 are expected. What’s Wrong?

Arguments Continued: Example 1 • For our second example we try “ Sub Count2MaxTest2() Dim lRow As Long Dim lCol As Long Dim lStartRow As Long Dim lMaxRow As Long ‘ we decide we want to start 5 down from the top anyway. lStartRow = 5 lMaxRow = 5 ‘and we’ll do 5 from the start For lCol = 2 To 10 Step 2 ‘loop over each column Call count2Max(lCol, lStartRow, (lStartRow + lMaxRow)) Next lCol End Sub “ • Calling the same Sub count2Max for rows as before then we get this->

Arguments Fixed: Example 1 • Finally we change Sub Count2Max()-> Count2MaxFix() & This Works: “ Option Explicit Sub Count2MaxTestFix() Dim lRow As Long Dim lCol As Long Dim lStartRow As Long Dim lMaxRow As Long lStartRow = 5 lMaxRow = 5 For lCol = 2 To 10 Step 2 Call count2MaxFix(lCol, lStartRow, (lStartRow + lMaxRow)) Next lCol End Sub Sub count2MaxFix(lCol As Long, lStartRow As Long, lMaxRow As Long) Dim lRow As Long ‘local lRow = lStartRow ‘make a local copy While lRow <= lMaxRow Cells(lRow, lCol).Value = lRow lRow = lRow + 1 ‘only increment the local copy Not lStartRow Wend End Sub “

A Word About Arguments, Example 2 • This is a little more subtle and slightly different but can be a real world problem when using the xutil.dll (library). Assuming the modXutil VBA wrapper code is in your sheet in modxutil: “… Declare Function xushl Lib "XUTIL.DLL" (lVal As Long, nBits As Long) As Long … Function VB_shl(lVal As Long, nBits As Long) As Long Dim lRet As Long lRet = xushl(lVal, nBits) VB_shl = lRet End Function “ • FYI: this is the C code the above calls: “DLLEXPORT unsigned long WINAPI xushl(unsigned long *ulval, unsigned long *nbits) //return lval shifted left nbits { unsigned long ulret; ulret= *ulval << *nbits; return ulret; }” • Then if one wrote (I did write) the Very Legal and standard looking code which did not work when I first tried it! Though it works on my current VBA & Excel Version and machine it is not to be trusted! “Sub aMaybeShl() ' this can and has failed! Dim lVal As Long Dim lnBits As Long lVal = 1 lnBits = 4 '2^4=8 lVal = VB_shl(lVal, lnBits) Cells(1, 1).Value = lVal End Sub “

A Word About Arguments, Fix, Example 2 cont. • By adding another Variable (that is at a different location/address/pointer than the one passed in guarantees that it will work: “ Sub aShl() ' never caught failing Dim lVal As Long Dim lnBits As Long Dim lValLshifted As Long ' make a separate variable at a different address to hold the result lVal = 1 lnBits = 3 ‘ 0001 -> moved 3 over = 1000 = 8 lValLshifted = VB_shl(lVal, lnBits) ‘Function input and ouput, lValLshifted and lVal, no longer share the same address and are totally isolated. ‘the result is safe even if lVal is late popping off the stack. The intent is preserved regardless of any CPU out of order problems and/or stack problem(s)! Cells(2, 1).Value = lValLshifted End Sub “ • Having separate variables at 2 different addresses provides extra insurance of some unintended interpreter error or glitch or whatever across languages and versions of) VB(A) providing much greater robustness. • Something to look out for and won’t hurt (very much) to make an extra variable when calling any similar routine(s) including all VB(A) ones! • Why use pointers and/or pass by reference? Not using them would make problems like this go away, right? Yes, but then in every call one would have to use the visual basic “byval” qualifier which I was trying to avoid! Was trying to make the C routines as native as possible to VB(A). Also many routines intend to change the argument values on return. Also see “arrays” later.

A Word About Arguments Example 3 • What happens when a constant is passed to a pointer where the C routine changes the argument? (On my XP (version 10.2) VB does the right thing and does not change the constant.) But when it’s passed into the C code what is going to happen???? • Technically it is not “best practice” to pass a constant to a routine that could change it! The literature is full of discussions of where the constant did get changed. (It might happen on an earlier version even though my machine did the right thing?) • An example is the Bit3 VB_initios(iAddMod,lBit3PhysicalBaseAdd,is406flg) call. All of these arguments are pointers. So the C code could potentially change say the lBit3PhysicalBaseAdd variable. So if the following code was being used for say an ISA Bit3 model 406 then the following would work be acceptable as that Bit3 must be base address at 0xd0000 to work (and the driver doesn’t change it.) “ Sub initVME1() Dim iADDMod As Integer Dim dtime2waitperlongword As Double Dim dRet As Double Const lADDCONST As Long = 851968 '0xd0000 iADDMod = Cells(2, 4).Value 'For the 6xx series the base address on the PCI bus is returned. 'for the 40x series (ISA bus) cells(3,4).value must be 0xd0000 or it will never init! Cells(5, 3).Value = VB_InitIOs(iADDMod, lADDCONST, True) Cells(3, 3).Value = "'" & Hex(lADDCONST) ‘(if it’s a 6xx) show the user it’s base address on the pci bus ‘ can be useful for diagnostics… End Sub

A Word About Arguments, Better Example 3 cont. • But say the sheet gets moved to a machine using say a 617 on the PCI bus?? Although I managed to keep the arguments the same across different models and busses the drivers function slightly differently. Apologies for this but I thought it o.k. to do this. (Worse was to change the number of arguments and then the blue screen of death might occur…). Anyway this is the way it is! • The 617 (also 618, 620 or just 6xx as opposed to the 403 or 406 =40x) drivers all use the 2nd argument to return the physical address of where the 6xx is on the bus! (Physical addresses of boards on the PCI bus are dynamic and can be moved around…. I returned the address on those as a debugging diagnostic for anyone interested. That’s another story we won’t go into today.) • So this is a fix: “ Sub initVME() Dim iADDMod As Integer Dim lAdd As Long lAdd = Cells(3,4).value ‘ originally most sheets picked up 851968 0xd0000 from here! iADDMod = Cells(2, 4).Value 'For the 6xx series the base address on the PCI bus is returned. 'for the 40x series (ISA bus) cells(3,4).value must be 0xd0000 or it will never init! Cells(5, 3).Value = VB_InitIOs(iADDMod, lAdd, True) Cells(3, 3).Value = "'" & Hex(lAdd) ‘‘(if it’s a 6xx) show the user it’s base address on the pci bus ‘ can be useful for diagnostics… End Sub

A Word About Arguments, Best Example 3 cont. • What could/should be improved on in the above? (for really armored argument protection and consistency? “True” is also an (intrinsic) VBA constant….. • Although none of my drivers ever assign that flag to anything it is NOT BEST PRACTICE! So this is the last best and final answer: “ Sub initVME3() Dim iADDMod As Integer Dim lAdd As Long Dim isModel406flg As Boolean isModel406flg = True lAdd = 851968 '0xd0000 or cells(3,4).value iADDMod = Cells(2, 4).Value 'For the 6xx series the base address on the PCI bus is returned. 'for the 40x series (ISA bus) cells(3,4).value must be 0xd0000 or it will never init! Cells(5, 3).Value = VB_InitIOs(iADDMod, lAdd, isModel406flg) Cells(3, 3).Value = "'" & Hex(lAdd) ‘(if it’s a 6xx) show the user it’s base address on the pci bus ‘ can be useful for diagnostics… End Sub“ • (Note that the isModel406flg argument is not used for anything on the model 6xx drivers so its state for these boards is ignored!) For the 40x drivers it tells the driver whether to send out the highest byte of the address on the bus or not. (A model 406 has 3 LED’s on the front while a 403 has none. (BTW: Models 617, 618, & 620 have the same 3 LED’s as well. An easy distinguishing feature is that a 617 cable has thumb screw tabs instead of plain slotted screws. Models 618 and/or 620 have orange light pipes instead of copper cable.)

About Arrays as Arguments • This needs to be mentioned because the VB help notes are misleading and/or not very helpful and limiting and a lot of the C DLL’s do much more than the Official Excel/VBA Help Notes: “ Understanding Parameter Arrays A parameter array can be used to pass an array of arguments to a procedure. You don't have to know the number of elements in the array when you define the procedure. You use the ParamArray keyword to denote a parameter array. The array must be declared as an array of type Variant, and it must be the last argument in the procedure definition. The following example shows how you might define a procedure with a parameter array. Sub AnyNumberArgs(strName As String, ParamArray intScores() As Variant) Dim intI As Integer Debug.Print strName; " Scores" ' Use UBound function to determine upper limit of array. For intI = 0 To UBound(intScores()) Debug.Print " "; intScores(intI) Next intI End Sub The following examples show how you can call this procedure. AnyNumberArgs "Jamie", 10, 26, 32, 15, 22, 24, 16 AnyNumberArgs "Kelly", "High", "Low", "Average", "High" “

About Arrays as Arguments Part 2 • In earlier help versions doing it their way limited the argument list to one array of type variant. (More on variants later). It was very limiting. Realizing everything is a pointer one can get around this limitation and just pass what you want! The syntax is a little like some C code and/or FORTRAN so if you are familiar with that then you should be all set! • Regardless of your previous experience here is a VB call to a C unpacker with many many arrays (which could be of different types… none are of type variant!) though in this one they are all (basically) all (32 bits) Long: “ lNumbChanErrs = svxunpackVRB012(lrawAry(0), lStartRaw, lNum16BitWords, _ lStartchip, lLastChipAry(lWhichVRB), lChipIDAry(0), lFirstBadIndex, lChipIDerrAry(0), _ lchipLastIDVal(0), lChanErrCountAry(0), lStoreDataAry(0), lnumBadChipIDs, ulAFEBoardID(0), _ pulFirstBadVirtual, pulVirtualBadCount, pulVirtualCounted, ulVirtualData(0)) “ Here’s the C *.h header declaration: “ DLLEXPORT unsigned long WINAPI svxunpackVRB012( unsigned long lDMAary[], long *plstartraw , long *plEndRaw , long *plStartChip, long *plLastChip, unsigned long lChipIDAry[], unsigned long *pulFirstBadIndex, // end of input args, beginning of output args unsigned long lchipIDerr[], unsigned long lchipLastIDVal[], long lChanErrCount[], unsigned long lStoreData[], long *plnumBadChipIDs, unsigned long ulAFEBoardID[], unsigned long *pulFirstBadVirtual, unsigned long *pulVirtualBadCount, unsigned long *pulVirtualCounted, unsigned long ulVirtualData[]); “

About Arrays as Arguments Part 3 • In the above the “_” signifies to VB that the next line is a continuation of the previous. (Some limit here of 5 lines? To overcome make longer lines! Have come close to the limit but has been long enough though I’m sure it could be broken…. Another solution is to make some of the arguments global if possible!) • If svxunpackVRB012() was written in VB then the (in this case function would be declared similarly without the (pointer indicator) “*” and using parenthesis instead of square brackets: “[“ and “]”. • When calling a subroutine or function as above one does not have to call it with 0 but can pass the array starting from any element (or variable indicating and element) in the array (say one wants to work on some increment of the array instead of the whole thing) by putting it inside the parenthesis instead of 0. The Caveat (exception) to all this (you knew there would be an exception right?) is that MULTIPLE DIMENSIONED VB arrays and C arrays are not compatible. One is organized by row, column and the other by column, row in memory so things get big time scrambled passing multiple dimensioned arrays. But there is a workaround! (You knew that too!). The answer is to use VB type declarations to make them multiple dimensioned “Collections” (a user defined type of array.) Then they can be passed to C routines with no problems! But the declarations must match exactly! (For array declarations in VB(A) it may make 1 more element than called for! Check the exact length in the debugger for your version! In C when you say int myAry[5] it will make 0..4. But VB(A) may make 6 to try to protect you from looping 6 times: 0 to 5. This is a very common mistake. The rule to remember is when counting N elements one declares them with N. When counting from 0 one only loops to (N-1)! When counting from 1, one loops to N. Both loops will go N times!) So with collections this must be accounted and corrected for very carefully! (For Collections VB(A) may make 5 elements, 0..4 as does C!) It’s a little goofy but it can be made to work in the end! (The final Caveat is that for really large “Collections” (>64K bytes), VB will not permit and/or handle them. No workaround at present except to go back to single dimensioned arrays and use modulo operations to go to the next one…. Not pretty but it can be “forced” to work.)

More about User Defined types! • What are they good for? They can help keep track of like things that go together instead of having a bunch of separate constants, variables and arrays. More object like. • When used with arrays they become a “Collection” which is another name for an array. (Collections have a useful property in that double dimensioned ones can be passed to C but arrays can not because internally arrays and “Collections” have opposite Row, Column memory order organization. So “collections” are very useful for VBA to C mixed language programming and can also be used with data unpacking routines.) This example is to make a structure holding the addresses of the registers for a DZero High Voltage VME module. The types below were originally translated from (Turbo) Pascal so the Pascal comment delimitters, “{ }” are still there. This example is from d0server4\users\angstadt\xls_teach\hv6_617.xls in the module “modHV”: “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 channels: 1 record type 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 for card } lMod_SN As Long 'word; { 16 bit card i.d. } End Type 'Global HVchanAdd(iLASTSLOT, iLASTCHAN) As Chantype '[0..5, 0..7]of Chantype; original Global HVmodAdds(iLASTSLOT) As Modtype '[0..5] of ^modtype ;” 1 module / sheet here! • Before using these structures one needs to init them in a routine like below and then be sure to call it before using any of the structures.

How to Use VBA & Excel with the Bit3 to VME