Wk 6 Resources

Resources (to be done in order)

 Description  Link
VBA in Excel Set up https://www.techonthenet.com/excel/macros/visual_basic_editor2010.php
VBA Fundamentals Resources
*Cover Home, Overview, Excel Terms, Macro Comments
https://www.tutorialspoint.com/vba/index.htm
Immediate Window (Only Point #4) https://www.excelcampus.com/vba/vba-immediate-window-excel/
VBA Fundamentals Resources
*Cover Variables, Operators, Decisions, Loops
https://www.tutorialspoint.com/vba/vba_variables.htm
Writing Data / Variables to Excel Cell https://stackoverflow.com/questions/17838652/vba-sheet-cellsstring-how
Code Presentation http://www.excelfunctions.net/VBA-Code-Presentation.html
VBA Functions https://www.tutorialspoint.com/vba/vba_functions.htm

Quick Notes

  • File > Info > Customize Ribbon > Check Developer Tab > Alt + F11 to open VBA Editor
  • Know what Modules, Functions, Sub-Procedures are
  • ‘ or REM to include comments in program
  • Ctrl + G to open Immediate Window. Use “Debug.Print” to print data into the immediate window.
  • Variables: String, Integer and others
  • Arithmetic Operators: + (add), – (subtract), * (multiply), / (divide)
  • Comparison Operators: = (equal), <> (not equal), > (greater than), < (less than), >= (greater than equal to), <= (less than equal to)
  • Concatenation Operators: If A = 10, B = 5: A+B=15, A&B=105
  • If Condition
    • if (condition) then
      • execute code
    • Elseif (condition)
      • execute code
    • Else
      • execute code
    • End If
  • For Loop
    • For i = 1 to 10
      • code
    • next i
  • Define worksheet as “ws”. ws.cells(row,column) = value to assign a value to a particular cell
  • Proper Indentation for Codes
  • Functions
    • function findArea (Length as Double, Length as Double)
      • code
    • end function

Exercises

  1. Write a simple program to print “Hello World” in the immediate window.
  2. Store 2 numbers from 0 to 100 in 2 different variables. Print the addition, subtraction, multiplication and division results of these 2 numbers.
  3. Store 2 numbers from 0 to 100 in 2 different variables. If No. 1 is greater than No. 2 print “No.1 > No.2”. If No. 2 is greater than No. 1 print “No. 2 > No. 1”. Else print “No. 1 = No. 2”
  4. Print numbers 1 to 50 using a For Loop.
  5. Print numbers 1 to 50 using a While Loop
  6. *Print numbers 1 to 50 using any loop in a continuous string (e.g. 1, 2, 3, 4, 5, 6…) (Hint use a string variable to store)
  7. Print results from Qn 2 in first 4 cells of the excel sheet
  8. Print results from Qn 3 in 1 cell on the excel sheet
  9. Print results from Qn 4 in the first 50 rows of the excel sheet.
  10. Print results from Qn 4 in the first 50 columns of the excel sheet.
  11. Print the resulting string from Qn 6 in the first cell of the excel sheet.
  12. *Write a function to convert kilometres into miles. (Refer to VBA Functions tutorial)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s