Cee3804_Excel_Macros_VBA_07

Cee3804_Excel_Macros_VBA_07 - Excel Macros and VBA CEE3804...

Info iconThis preview shows pages 1–12. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Excel Macros and VBA CEE3804 Computer Applications for Civil and Environmental Engineers Fall 2007 CEE 3804 2 Topics to be Covered Excel Macros Understanding and making use of VBA Basics of VBA Using code modules Understanding procedures Interacting with the user Creating useful forms Adjusting form layout Using form and control events Fall 2007 CEE 3804 3 Macros Definition A macro is: a series of commands recorded within the user interface and wrapped into a single action A procedure is: is a series of actions but, unlike macros, a procedure is written from scratch with the Visual Basic for Applications (VBA) programming language In summary: a series of commands is called a macro when it is recorded, however, a macro is a procedure within the VBA world Fall 2007 CEE 3804 4 Macros Why use Macros? Why use macros?: to simplify a series of commands by automating the task simplify complex tasks to learn how the VBA language lends itself to the Excel environment Fall 2007 CEE 3804 5 Macros Recording Macros Recording macros: Tools/Macros/Record New Macro Macro recorder is impartial: should map out exactly what you are trying to do overall goal of macro cells that will be selected data required by macro menu command to accomplish task workbooks that will use the macro Give macro a descriptive name and shortcut Indicate relative versus absolute references Fall 2007 CEE 3804 6 Macros : Recording Excel 2007 Look for the Developer Tab Excel 2003 Look under Tools/Macro/Record New Macro Fall 2007 CEE 3804 7 Macros: A Simple Example A macro that creates a template for your homework assignment is shown below Note: absolute references Fall 2007 CEE 3804 8 Macros: Relative References Useful when you need to start the macro at any location in the worksheet Note: relative offset notation Fall 2007 CEE 3804 9 Macros Example Create a macro called Title_Logo: Goes down one row and types the following title: Virginia Tech Civil and Environmental Engineering Department Makes the text bold Inserts the date in the cell below the title using the 04-Mar-00 format In Excel 2003 open the Visual Basic editor to view the code: Tools/Macros/Visual Basic Editor or Alt+F11 Fall 2007 CEE 3804 10 Macros Example Sub Title_Logo() ' ' Title_Logo Macro ' Macro recorded 2/7/00 ' ' Keyboard Shortcut: Ctrl+t ' ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Virginia Tech Department of Civil and Environmental Engineering" Selection.Font.Bold = True ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=TODAY()" Selection.NumberFormat = "dd-mmm-yy" End Sub Fall 2007 CEE 3804 11 Macros Storing Macros Macros can be stored: This workbook macros specific to the workbook New workbook Excel generates a new workbook to store the macro advantage: multiple workbook applications can share the...
View Full Document

Page1 / 68

Cee3804_Excel_Macros_VBA_07 - Excel Macros and VBA CEE3804...

This preview shows document pages 1 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online