VBA: Short Lecture Notes

Steps/Pointers for doing your code:

• Outline what you need to do in little modules.
• For each major procedure make an outline
• Try out the procedures on the spreadsheet 
•  Automate using the macro 
• Clean up the macro and do your editing. (Doing back engineering)
• Put your procedures together
•   Put your favourite codes together
• Stress test your code
• When in doubt, Use Help.

Some Clarifications

• Procedure: This is a basic set of codes that gets executed: Sub and End sub
•  Functions: creates a dynamic output: Function and End Function
• Modules: see all of your code

Variables: Common Data Type

• It must really be defined.
• Common Data Types
• Integers
• Long
• Single
• Double
• Decimal
• Date
• Object
• String - text
• Variant – any kind
• String x integer = error, so define the variables beforehand
• It entails dimensioning
• Data type Summary on VBA Help for more information

Declaring Variables 

• Code: Dim a as Integer, b as integer
• Declaring arrays
• Dim histdata(1 to 100) as double - it has 100 items
• Dim matrix(100,100) – it has 101 x 101 items

Looping

• For next
• For each next -
• Do while Loop – continue running until true
• Do until 
• Do – can endlessly run

If then Statements

     If a<b 

       Variable = 0

     Else
       Variable = 1
     End if
  

 Go to statement

• Changes the flow of the procedure
• BEST used for error trapping
• Abruptly change the code..
• Example: if variable = 1000, go to 170 (which is a statement outside the loop)
• Best used for error trapping

Case Constructions

• Works like if then statement, very handy for multiple cases

      Select case statement
         Case is <.5
            Procedure for case
         Case else
            Procedure

      End Select
                           
Visual programming - Forms

•  Codes are built around Forms
• In forms event are activated
• Left mouse click
• Click
• Enter range of values
• Working around the form: example: where you can capture the data at outputs the regression
• Form contain objects such as references, command button etc.
• Build form on arrays

Making Add-ins in excel