excel wth vbaVBA In Excel and Access
The purpose of this term’s work is to enable you to create Pivot Tables programmatically from an Access database using VBA with Excel/Access.
Excel is widely used for reporting, especially analysis of financial information etc. The tool mostly used is the Pivot Table/|PivotChart to summarize the information. RSC used the PivotTable quite extensively for their CTC (Cost to Company) analysis.
Microsoft also uses a programming language, called VBA, to program all of the Office applications. We are going to learn a bit about the VBA language and Pivot Tables/Charts in this semester.
Creating Pivot Tables/Charts automatically:
Firstly you will have to work through the Pivot Table and Chart tutorial that I have downloaded from the Internet – it will be on the usual drive. This will show you how to create a Pivot Table from data given in an Excel “database” using the wizards available. An Excel “database” is actually just a long list of repetitive data captured in Excel – you will see how the Pivot Table is used to summarize the data into useful information. The word pivot indicates that you can play around with the information – for example you can see the total sales per salesperson per quarter as well as the yearly total for that salesperson. By just moving one field, you can pivot the information to show you the quarterly sales for all the salespersons as well as the total sales for all the salespersons for that quarter. I will explain in more detail in class; ensure to take notes.
The second part is to show you how to program Excel using VBA – very similar to using VB.NET; BUT the big difference is the components you work with. In VB.NET you work with forms, text boxes, radio buttons etc. In VBA for Excel you work with workbooks, worksheets, ranges and eventually with Pivot Tables.
A macro is an application (program) written in VBA. You can ask Excel to write the macro for you, or you can write the program yourself. For the first few programs we will use the macro recorder to write the program and as we get more comfortable with VBA, we will the write the programs ourselves.
Recording a Macro:
First ensure that the Developer tab is available in the Excel Ribbon. If not, click the Office button (at the top of the workbook, on the left) -> click on Excel Options at the bottom of the menu list (Figure 1) -> check the check box next to Show Developer tab in the Ribbon (Figure 2) -> OK.
Select Developer -> Record Macro from the Ribbon (Figure 3). A window will appear – change the name of the Macro to something more descriptive (the default name of the macro is Macro2 for this example). Click OK.
3. Highlight the numeric cells on the spreadsheet, click Format –> Format Cells on the Ribbon (Figure 4) and select the Currency option under the Number tab; change the Font and the Background color as well. Click anywhere outside the selected range to deselect.
To stop recording the macro, go to the Developer tab -> Stop Recording (blue square) on the Ribbon.
Visual Basic Editor:
To see the macro you have just created, you will have to go to the Visual Basic Editor (VBE). There are two ways of going to the editor:
The first way is also the general way; Developer tab -> Visual Basic from the Ribbon. 2.
The second way enables you to go straight to the macro (program) you need to see or edit. You will select Developer tab -> Macros. From the screen that then appears (figure 5), select the macro you want to edit and click the Edit button on the right hand side of the screen. If you want to repeat the actions of the macro; you can Run the macro from the button on the right hand side of the screen. To test the macro you can change the format of the cells and then run the macro. Figure 5:...
Please join StudyMode to read the full document