L7 – VBA Workbook, Sheets, and Cells

[Pages:3]L7 ? VBA Workbook, Sheets, and Cells

Introduction

The objectives of this exercise are to have a hands-on experience with basic knowledge of programmatically accessing different components of a spreadsheet and debugging techniques. In this exercise, you will just follow procedure to complete a task and submit a PDF printout of the exercise as your lab report.

Procedure

We will create a Hello World VBA program that displays messages, manipulate contents of sheets and cells, and extract information from cells.

1. Create a User Form: Create a new spreadsheet file with your Microsoft Excel and make sure you have the Developer's tab available. If you do not have it available, follow the procedure given in previous lecture to make it available. Click Visual Basic in the Developer Tab and create a new User Form.

2. In the Properties windows, change the Caption property of the User Form to "Lab 7", Change the (name) property to "frmMain", and the Show Modal property to TRUE. When Show Modal property is TRUE you will be able to access other Windows objects while your VBA is running. When Show Modal property is FALSE your User form is fixed to the foreground and you cannot access any other Windows Object. Try this by running your VBA program and click the spreadsheet while the user form is active.

3. Go to your spreadsheet by clicking the Spreadsheet ICON on the upper left most corner of the Developers Ribbon (make sure your program is not running at this time) and save your spreadsheet file with SAVE AS... In the Save As File Type box choose Excel MacroEnabled Workbook and give your file a name. A macro-enabled excel file has your embedded VBA code included. If you save the file as another type, for example .XLSX then the next time you open your file, the VBA code you have done will be missing.

4. On the user form (frmMain) add the following objects: two text boxes and name them txtMessage, and txtValue; two labels and name them lblMessage, and lblMyName; a command button and name it btnOK. Position lblMessage above txt Message as if it is a label for the txtMessage. Change the Caption of lblMyName to your name and NetID

5. Add information into each of the ControlTipText property of the objects in the frmMain.

L7 ? VBA Workbook, Sheets, and Cells

6. What we want to do is to write VBA codes for some events associated with some of the objects and write a VBA subroutine to manipulate the spreadsheet.

7. When the user form is first opened, we want to initialize lblMessage with "Hello World", txtMessage is cleared, txtValue has "0" in it, btnOK has its caption = "Hello". To do this we need to have a piece of code written in the _ACTIVATE method of frmMain. Double-click on the user form and a VBA coding window will open with a stub subroutine for the _CLICK method is created (a stub subroutine is an empty subroutine with only the name of the subroutine and the END SUB keywords inserted. It is important not to modify the name of the subroutine). From the drop-down box on the upper right of the coding windows select Activate method and a subroutine stub will be created, then inside the subroutine insert the following code segment and after that run your VBA program and see what happens: lblMessage.caption = "Hello World" `apostrophe is comment in VBA txtMessage.Text = "" `empty string is quote-quote. We make txtMessage to contain empty string txtValue.text = "0" `assign 0 into the text property of txtValue btnOK.caption = "Hello" ` we change the caption of the command button

8. Put 999 in cell A2 of the spreadsheet then go back to the VBA editor window. We want to do the following: when the user clicks the command button we want to grab the text of txtMessage and put it into cell A1 of the spreadsheet, and we want to get the value from A2 of the spreadsheet and put it in txtValue, then we want to display a message that says "DONE" to the user. This code must be put in the _CLICK method of the command button btnOK. Doubleclick on the command button to open the VBA coding window and the _CLICK event subroutine stub is created by default. In the _CLICK event subroutine write the following segment of code: range("A1").value = txtMessage.text `this assigns the text content of txtMessage into cell A1. The same result can be obtained by using cells(1,1) = txtMessage.text txtValue.text = range("A2").value `get the value from A2 and put it in txtValue. msgbox "Done!" Run the program and enter a string into txtMessage then click the command button of your user form and see your code in action.

9. In the VBA editor window create a new MODULE. With the newly created module selected the coding window belongs to the module, which means every subroutine you create in this window will be put inside the module.

10. Create a subroutine called Replicate by typing the following into the coding window to create the stub for this subroutine: sub Replicate `This subroutine replicates the content of txtMessage 30 times into cells B1 to B30

11. In the Replicate subroutine enter the following segment of code: dim s as string `DIM is a keyword for declaring a variable. We declare s as a string

variable dim n as integer `we declare n as an integer variable s = txtMessage.text

L7 ? VBA Workbook, Sheets, and Cells

for n = 1 to 30 ` the syntax of a FOR loop consists of FOR and Next. If you use FOR n to begin the FOR loop... then you must use Next n to end the for loop

cells(2,n) = s next n 12. We want the Replicate subroutine to execute when the user double-clicks the user form (frmMain) at run time. So, we will need to put some code in the _DOUBLECLICK event method of the user form. Do like Step 7 except this time we want to do _DoubleClick event and type the following segment of code in the _DoubleClick subroutine stub: Replicate `this calls (executes) the Replicate subroutine 12. Print your VBA into PDF: From your VBA developer environment, select the user form in the Project Window (the left column window) the right-click and choose Print.... Select Form Image and CODE then click the SETUP button to open a printer dialog window. Select your PDF printer and print it to PDF file. Do the same for your Module except your module does not have form image. Then combine (merge) the two PDF files into one and submit it as your lab report.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download