Teachershare : VBA

1. Teachershare: VBA Ilia Avroutine Royal Grammar School High Wycombe, Bucks

2. Bridges the divide between ICT and Computing • Start small – with Excel formulas, combining them • Replace them with user functions • Create validation rules • Play with shapes • Add forms and more Visual Basic • Great experience designing interfaces and brings forward the notion of ergonomics • Bring on API calls and OOP

3. Bridges the divide between ICT and Computing • Easy syntax, especially if it follows Scratch/Byob and potentially uses block names as variables/function names • No semicolons at ends of lines, no cryptic brackets/voids • Has incredible wealth of information on the Internet • Can be used to extend the power of Excel for research and modelling – useful for other subjects and in University • Pupils can program at home or any computer without needing to install tricky packages (like JAVA) • Is similar to Basic in the Open Office – making the skills multi-platform transferrable, plus similar to Small Basic

4. Subs/Functions/Forms/Visual controls/File Access • Great to have easy saving in spreadsheets – great for illustrating arrays • Supports Records – native support for Access files via DAO • Limited OOP support • Rapid prototyping

5. Useful for office employment and automation • Ability to use Windows APIs • Active X controls • The code can write itself – Skynet? • Most skills easily transferrable to other languages – as our pupils demonstrated, learning ECMA languages on their own • Any version of Office should work starting from Office 2000 – nothing new to install • Very visual • Can be extended into ASP, Vbscript

6. Downsides: • Slow and verbose • Has no built-in timers (API is a workaround) • Doesn’t indent automatically • More different from pseudocode than Pascal • Is a dead-end language in itself, is quite old-fashioned • No proper OOP inheritance (but there are workarounds, this is Microsoft, after all) • Runs on top of an office application • Requires relaxing Macro protection – potentially, a security issue

7. Transition to other languages • Pupils are encouraged to have a Word file with accumulation of all the code they’ve written throughout the years • We aim to use our VLE Wiki/Glossary feature to create a repository of code they understand as it’s at their level

8. Step 1: • Logical functions in Excel • Introduce the concept of pseudocode • Copying and pasting Excel functions to other cells substitutes cells’ addresses – brings a discussion of variables and constants

9. Step 2: User Defined Functions • Start easy – a function for converting between metric and imperial • Then introduce polynomial functions with conversions from degrees C to F • Then test their pseudocode with MPG to Litres/100 km formula (2/3rds of them usually can’t do it even at this stage) • Introduce functions with multiple parameters • Array functions – great for anybody who uses Excel anyway • Generation of random numbers and series – Excel gives us the ability to plot them – nice and visual

10. Step 3: Data handling Forms • Introduction to Modules and Forms • Visual Controls • Variable scope • Proper programming information • Might use forms to extend the User Defined Function examples done previously • Use rich content on forms – Google maps, movie files, mp3 playlists • Form navigation and interface maps

11. VBA is not just for programmers • The industry needs good interface designers, system architects – all of these concepts can be easily taught in VBA without getting bogged down with “proper” programming which might put some of the weaker pupils off

12. Using Office shapes for modelling • Railroad crossing • LCD multi-segment digits • Pong • Cash Machine • Battleship • Under development: Pacman, Space Invaders, Arkanoid, etc.

13. Forms can also be used for games • Pupils will face some of the same limitations as the early designers in 1970s – no strong graphics, so have to use very basic objects

14. Agenda • Rock, Paper, Scissors game via 2 random number generators • Discussion of pseudocode for the machine-accessible rules of winning • Use Excel formulas (Lookup, IF) • Show equivalent VBA function • MPG to Litres per 100 Km conversion • Discussion of pseudocode • Implementation

15. Temperature Conversion (from http://www.plvwtelco.net) • Convert Fahrenheit to Celsius • 1.Determine the temperature in Fahrenheit • 2.Using your calculator, subtract 32 • 3.Multiply the result times 5 • 4.Divide that answer by 9 • 5.Your final answer is the temperature in Celsius • For the math purist, the formula is (F-32)*5/9 = C • No calculator handy? Take Fahrenheit, subtract 30, then divide by 2 • Convert Celsius to Fahrenheit • 1.Determine the temperature in Celsius • 2.Using your calculator, multiply the temperature times 1.8 • 3.Add 32 to the result • 4.Your final answer is the temperature in Fahrenheit • For the math purist, the formula is (C*1.8)+32=F • No calculator handy? Take Celsius X 2, then add 30

16. VBA at a glance (assuming Office 2007 but others will work, just different screens) A spreadsheet is a giant 2D array (3D if you consider using multiple sheets) VBA gives pupils access to all maths functions of Excel directly Also – full access to graphics abilities for plotting and tabulation

17. VBA is hiding behind ALT+F11 or...

18. Now, your Excel has Developer menu that you can use to enable Macros

19. Make sure that Macros are enabled Macro viruses are no longer a threat with new version of MS Office, so you should be ok. Nothing replaces a proper antivirus program

20. VBA Editor window

21. Familiar VB syntax – so, can leverage all the VB literature The new bits – VBA objects, right-click this area to add Code modules and Forms

22. VBA objects: • Workbooks • Sheets • Shapes • Forms • Ranges (cells) • Any ActiveX object – from Quicktime video to Flash movie • Use Macros to explore VBA objects

23. Here is a simple example – a user defined function converting centimetres to inches

24. Give it a meaningful name. You can have as many as you like

25. The function code itself... Functions take parameters and are typed Function cm2inch(ByValinputCM As Single) As Single cm2inch = inputCM / 2.54 End Function

26. The moment you are finished coding the function, you can switch back to the spreadsheet view and Excel will recognize your function and add it to its built-in list

27. Behaves just like native functions and takes a cell that you click on as a parameter Function cm2inch(ByValinputCM As Single) As Single cm2inch = inputCM / 2.54 End Function

28. The result, with some extra formatting

29. Something a bit more involved. VBA being partially OOP, we have arrays(1st function) and also collections(2nd function below) Function sumOfCubes(ParamArrayotherElements() As Variant) Dim counter As Integer For counter = 0 To UBound(otherElements) sumOfCubes = sumOfCubes + otherElements(counter) ^ 3 Next counter End Function Function sumOfCubes2(ByValotherElements As Range) Dim element As Range For Each element In otherElements.Cells sumOfCubes2 = sumOfCubes2 + element ^ 3 Next End Function

30. That’s it for the intro into VBA. Some very good links • http://www.cpearson.com • VBA in a Nutshell • VBA for Engineers • Excel Archives • Windows API list • 10 VBA Tips from Microsoft • Interacting with Access Databases • General Excel and VBA resources