Visual Basic (MS Excel)

Exercise 1

1. Start Microsoft Excel
2. From the View menu select Toolbars and then Visual Basic
3. Select Visual Basic Editor from the toolbar (remember that if you hold the cursor/pointer over an item on the toolbar, an explanation will pop up)
   • A new window has now opened and the excel spreadsheet is now in a separate application.
4. Select Insert Userform from the toolbar below the drop down menus.
5. If the Toolbox is not already showing, select Toolbox from the View menu.
6. Select Textbox from the Toolbox
7. Draw a textbox on UserForm1 (Click the leftmouse button on the form, hold it down and drag it over an area about 3 grid points tall and about 10 wide, then let up the button)
8. Look for a window called Properties - TextBox1
9. Change the property called (name) from TextBox1 to M
   • replace TextBox1 on the right hand side with the letter M
   • Click back on UserForm1 when finished to get the Toolbox to reappear
10. Now draw 2 more text boxes on the form and give them the names V and D (repeat the last 4 instructions twice)
11. Select Label from the Toolbox
12. Draw a label (about 3 x 8 gridpoints) on the form just beside the first textbox (the one you renamed M - you can click on any object on the form and look at the properties window to check which is which)
13. Look for a property called Caption in the properties window.
14. Change the Caption property from Label1 to Mass
15. Now create 2 more labels beside the other 2 textboxes and set the captions to Volume and Density (repeat the last 4 instructions twice)
16. Select CommandButton from the Toolbox
17. Draw a button on the form (about 3 x 8)
18. Change the Caption property of the button from CommandButton1 to Calculate
19. Right click (click with the right mouse button) on the new button you have created, and select View Code from the pop-up menu.
20. Enter the following instruction between the Private Sub.. and the End Sub lines: LET D=M / V
21. To run the program, select Run Sub/User Form from the toolbar below the dropdown menus. (You should now see the form without the grid points on it)
22. Enter 2 numbers in the Mass and Volume textboxes (avoid using zeros - why?)
23. Click on the Calculate command button (you should get the correct value for the density - try it several times)
24. Click on the X in the top right hand corner of the Userform1 window to finish running the program.
25. To see the form again select Object from the View window.
26. To save your work, select Close and Return to Microsoft Excel from the File menu.
27. Select Save from the File menu and save it on drive H: under the name vbex1.xls
28. Before going on to the assignment, close this workbook (File|Close) and open a new one (File|New|Workbook)

Assignment 1

Create a new form that will allow you to enter values for Initial Velocity (U), Acceleration (A) and Time Elapsed(T) in 3 text boxes and which will then calculate and place in a text box the distance travelled (S) using the formula: s=ut+(1/2)at²

Save the whole spreadsheet as VB1.xls on your drive h:

If you have any problems with understanding the instructions on this page you could email Brian Mulligan who will try to improve the page for future users and may be able to give you some help in the meantime.