1 / 15

VBA session 3 Paul Rubinov

VBA session 3 Paul Rubinov. My job: diverse audience so Bore you for 15 minutes. Confuse you for the other 15 min. Feel free to contact me rubinov@fnal.gov Disclaimer IANAP (I am not a programmer) We are not covering language syntax – but your book does. VBA session 3: Introduction.

joy
Download Presentation

VBA session 3 Paul Rubinov

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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@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, SHIFT+F9 • print stuff: “debug.print” and CTRL+G(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 Destination:=Range("C1:C3"),Type:=xlFillDefault 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 A’s “listproc”. • Only “modMain” is relevant but feel free to click around • Idea : • save parameters in excel rows: one row=1 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 A:“Step #”: • cell A7 should be 1, cell A8=2, 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 > 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 = 10*”Some 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 A’s utility routines • Search for: XUTIL.DLL Ex4: Example of Bob A’s 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? It’s true: look here

More Related