1 / 28

VBA for Excel

VBA for Excel. What is a spreadsheet?. An Excel spreadsheet is a set of worksheets Each worksheets is made up of rows and columns of cells Rows are numeric: 1, 2, 3, … Columns are alphabetic: A, B, C, … A cell contains data, or a formula. Cell Ranges. B6:E13. column letters. A1

jaime-mcdowell
Download Presentation

VBA for Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. VBA for Excel

  2. What is a spreadsheet? • An Excel spreadsheet is a set of worksheets • Each worksheets is made up of rows and columns of cells • Rows are numeric: 1, 2, 3, … • Columns are alphabetic: A, B, C, … • A cell contains data, or a formula

  3. Cell Ranges B6:E13 column letters A1 Single cell G2 : G8 Single Column Range A3 : E3 Single Row Range Read as “B6 to E13” Row numbers

  4. Numerical Data • Numerical data is any number (formatted or not) • Numerical data can be used in a formula • You can use Format/Conditional formatting with to alter the appearance of numeric data • Examples: 5344 -34.5209 75%

  5. Formatting Numbers

  6. Formatting Dates

  7. Formulas • A formula represents a mathematical calculation • Always starts with = • A formula contains: • Numbers • 25.32, -56, 78 • Cell addresses and ranges • A3, G24, A2:A54 • Mathematical operators • Excel built-in functions • Today, Month, Sum =100*B5-D3+sum(R2:R15)

  8. Today and Month functions • =TODAY() • =MONTH( a date)

  9. Macros in Excel • A macro is a sequence of steps designed to accomplish a particular task • Just like a sub procedure in VB • In Excel, a user may create a macro in 2 ways • Record a sequence of steps in Excel using the macro recorder • Write a VB sub procedure using the macro editor

  10. Macro Statements • A Macro always begins a Sub statement • A Macro ends with an End Sub statement

  11. To perform multiple actions on the same object Macros use With End With A Macro

  12. The Selection object • Just as a user can select multiple cells simultaneously, a macro can select a region of a worksheet and apply changes to all the cells in that region

  13. Recording a Macro

  14. Putting a button onto a spreadsheet—in CS132 we use Control Objects • We can create a button and assign it to a Macro • This button is called a button control • View/Toolbars/Visual Basic • Then choose the button control

  15. Adding the button control

  16. The Visual Basic Toolbar View/Toolbars/Visual Basic Check Box Text box Command Button Option Button List boxes Label Image

  17. Using the Control Toolbox & Property Box • Click on the tools icon to display the controls toolbox • After you add a control, right click on it to display the properties box

  18. An object and its properties Sample Command Button name = cmdStart caption = text that appears on the button (Start) enabled = true or false You must follow good programming practice • Object names must be meaningful • cmdExit • cmdStart

  19. Accelerator Property s ( not S) • This is done by putting the desired letter in the Accelerator property in the properties box • (note lower case only matters if more than one letter is present)

  20. Referring to a Worksheet in VBA • An index number is a sequential number assigned to a sheet, based on the position of its sheet tab • The following procedure uses the Worksheets property to activate worksheet one in the active workbook. Sub FirstOne() Worksheets(1).Activate End Sub

  21. Cell Ranges in VBA Properties Range("A1").Value = 99 The Value property is what the cell holds, in this case 99 Range("B2:B7").Interior.Color = vbYellow This changes the color of the cells in "B2:B7" to yellow. Methods We can use Range(“A1”).Select This makes “A1” the active cell

  22. A1-style references using the Range property

  23. Cells Notation • Cells(2,2).Value refers to R2C2 below Remember, row first, then column B3 is the same as Cells(3,2)

  24. Cells vs. Range Range("B1").Value = 8 Range("B2").Value = 8 Can also be written: Cells(1,2).Value = 8 Cells(2,2).Value = 8

  25. Deleting data • You can clear the contents of a range using the ClearContents method: Private Sub cmdClear_Click() Range("D:D").ClearContents End Sub • Note: ClearContents does not clear the formatting, just the numbers or formulas contained within the cells.

  26. Event handling for VBA objects • The various objects present in an excel application (application, worksheet, etc.) generate events that can be handled • Working with such events is beyond the scope of this class

  27. Documentation http://msdn.microsoft.com/office/understanding/excel/documentation/default.aspx

More Related