VBA session 3 Paul Rubinov

1
VBA session 3Paul Rubinov

• My job diverse audience so
• Bore you for 15 minutes.
• Confuse you for the other 15 min.
• Feel free to contact me rubinov_at_fnal.gov
• Disclaimer
• IANAP (I am not a programmer)
• We are not covering language syntax but your
  book does

2
VBA session 3 Introduction

• When we programmed in Pascal or C or Fortran, we
  wrote what we needed. In VBA (and VB), you try
  to find the thing (object/control) that does
  what you want.
• Key to learning VBA is knowing that something CAN
  be done.
• Record lots of macros.
• Give yourself a project.

3
VBA session 3 Intro cont.

• Must have skills
• record macros and play with them!
• use the help system (F1)!
• use step/step into F8/SHIFT-F8
• break points and watches F9, SHIFTF9
• print stuff debug.print and CTRLG(remember
  you can also change variables in the immediate
  window)
• copy others people code!
• If you master these skills, you are 90 done

4
VBA session 3 Intro cont.

• Type some numbers
• Type some formulas
• Play with formatting
• Add a plot
• Etc.
• but do it on your own time.
• For now, open Example1.xls

5
VBA session 3 Example1

• VBA has a very rich syntax - too rich. It has a
  lot of history
• Open example1.xls
• Press Alt-F11
• Double click on Module1
• Put cursor anywhere after sub
• Press Ctrl-G
• Press F8- and keep pressing!

6
VBA session 3 Example1 cont.

• Now double click on module2
• By now you are used to x.y.z
• but notice this line
• Selection.AutoFill DestinationRange("C1C3"),Typ
  exlFillDefault
• This is using named parameters
• Put your cursor anywhere inside the word
  AutoFill and press F1.
• so no big deal- this is just a function call
• If you get an error message when you try this,
  you do not have help installed for VBA. It is not
  installed by default!

7
VBA session 3 Stepper

• The stepper (/looper)
• open skeleton_stepper.xls
• This is a simulated stepper to demo the idea
  (inspired by Bob As listproc.
• Only modMain is relevant but feel free to click
  around
• Idea
• save parameters in excel rows one row1 step
• run many events with each parameter step
• display summary results on screen/store detail in
  file, if needed

8
VBA session 3 Stepper cont.

• Comments
• not meant for use by other people, but
• Basic instruction press the RUN button
• Main sheet is called MAIN
• Column AStep
• cell A7 should be 1, cell A82, etc.
• if the last number in this column is 1, it will
  loop
• if any number is 0, it will stop
• Column B Evnt/step
• number of events per step- should be gt 0

9
VBA session 3 Stepper cont.

• Basic instruction (cont.)
• Column C Some parameter
• an example adjusts the average- should be 5 to
  15
• Column F
• progress report number for successes/number of
  tries
• Column H and the following 63 columns
• random data, standard distribution with StdDev
  10 and mean 10Some parameter
• Sheet Profile
• simple histograms, always accumulating

10
VBA session 3 Random examples

• This is all from the Stepper
• to find, press CTRL-F in VBA, make sure
  Current Project radio button is pressed and
  search for the highlighted word
• Ex1 calling windows DLLs
• Public Declare Function timeGetTime Lib
  "winmm.dll" () As Long
• Declare Sub Sleep Lib "Kernel32.DLL" (ByVal
  dwMillisecconds As Long)

11
VBA session 3 examples cont.

• Ex2 If you have a driver for Visual Basic,
  chances are, it will work in VBA
• ScopeLibrary module (search for Hewlett-Packard)
• VBIB/NIGLOBAL modules (search for GPIB)
• Ex3 Example of Bob As utility routines
• Search for XUTIL.DLL
• Ex4 Example of Bob As routines for performance
• Search for MCMUTIL.DLL

12
VBA session 3 examples cont.

• Ex5 You can see an example of the use of STATIC
  variables in module
• Search for Static NumEvts
• This module is used in the skeleton version of
  the stepper, so you should be able to debug it,
  see how it works.

13
VBA session 3 examples cont.

• Ex6 You can create your own objects by defining
  a new Class
• Search for Class_Initialize
• This is also an example of using an external
  object (in this case the Scripting class)
• And also an example of file access using the
  FileSystem object contained in the scripting
  library

14
VBA session 3 Conclusion

• VBA is the essence of RAD
• Rapid Application Development
• or in English
• Easy tool for Quick and Dirty software jobs
• If you have a PC on your desk or lab bench you
  can do more/faster with VBA

15
VBA session 3 Resources

• Microsoft is the mothership you may wish to
• Start Here
• also try here or here
• http//www.beyondlogic.org/ is a good place to
  start searching for all kinds of interfaces
• Here are some fun FAQs
• A good place to learn about VB is here
• Did you know there is a free version of VB? Its
  true look here