vba session 3 paul rubinov n.
Skip this Video
Loading SlideShow in 5 Seconds..
VBA session 3 Paul Rubinov PowerPoint Presentation
Download Presentation
VBA session 3 Paul Rubinov

Loading in 2 Seconds...

play fullscreen
1 / 15

VBA session 3 Paul Rubinov - PowerPoint PPT Presentation

  • Uploaded on

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.

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

PowerPoint Slideshow about 'VBA session 3 Paul Rubinov' - joy

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

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

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
vba session 3 paul rubinov
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
vba session 3 introduction
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.

vba session 3 intro cont
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

vba session 3 intro cont1
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

vba session 3 example1
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!
vba session 3 example1 cont
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!

vba session 3 stepper
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
vba session 3 stepper cont
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
vba session 3 stepper cont1
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
vba session 3 random examples
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)

vba session 3 examples cont
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
vba session 3 examples cont1
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.

vba session 3 examples cont2
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

vba session 3 conclusion
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
vba session 3 resources
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