Visual Basic for Applications in Microsoft Excel (1)

1
Visual Basic for Applications in Microsoft Excel
(1)

• Week 5

2
Week 5

• Recap For Each..Next
• Personal Macro File
• Immediate window
• Object Variables
• Working with worksheets and workbooks
• For Loops

3
Last week

• For Each loops

4
Creating a Personal Macro Workbook

• Your personal macro workbook can be used to save
  the macros you record.
• Macros in this workbook are available to you
  regardless of the books you currently have open.
• You can access this workbook through the VBE

5
Personal Macro Workbook

• Record a new macro called myRed which formats the
  selected cells to red font. At the New macro
  window, change the Store Macro option (to
  Personal)
• Open the VBE and edit the macro so that the
  selection is made Cyan

6
Immediate Window

• You can use the Immediate window to test
  references and / or debug snippets of VBA code.
  Its quicker than having to write a Sub just to
  test one or two lines.
• ? in the Immediate Window means print the
  value of
• Whereas you can execute a statement by typing it
  and pressing Return egActiveCell99puts 99 into
  the active cell on the active worksheet in the
  active workbook

7
For Loops

• Alternative For Loop syntax
• Sub NameRange()
• Dim i As Integer
• With Range("A1")
• For i 1 To 10
• Range(.Offset(i, 1), .Offset(i,
  1).End(xlToRight)).Name "Week" i
• Next i
• End With
• End Sub

8
Object Variables

• We have declared range variables
• Other object variables include
• Workbooks
• Worksheets
• Charts

9
Object Variables

• You can declare variables of the type Object,
  where Object is a valid object reference e.g.
• Dim w as Worksheet
• Dim r as Range
• There is no Cell object
• The key word Set assigns a value to an object

Sub ObjectTest() Dim c As Range Set c ActiveCell MsgBox "The value in the activecell is " c.Value End Sub
10
Assignment with Set

• Set s Worksheets(1)
• Set s Worksheets("exams")
• are valid assignments to a worksheet object
• After an assignment like this you can use any
  method or property of the worksheet by referring
  to the object variable e.g.
• s.Activate
• s.Visible Not s.Visible
• s.Visible True

11
Exercise

• Write a macro in the VBE to declare a worksheet
  object, set it as Sheet 1 and rename it as Monday

12
Assignment without Set

• In a For Each loop Excel will treat a range as a
  single cell

Sub inc_rates() Dim mycell As Range For Each mycell In Selection mycell.Offset(0, 1) mycell 1.1 Next mycell End Sub
13
Assignment without Set

• This only applies inside the loop
• In this case the mycell reference has not yet
  been assigned

Sub inc_rates() Dim mycell As Range If IsNumeric(mycell.Value) Then For Each mycell In Selection mycell.Offset(0, 1) mycell 1.1 Next mycell End If End Sub
14
Assignment without Set

• Once inside the loop the range takes the
  reference and can be referred to by any code
• Corrected

Sub inc_rates() Dim mycell As Range For Each mycell In Selection If IsNumeric(mycell.Value) Then mycell.Offset(0, 1) mycell 1.1 End If Next mycell End Sub
15
Used Range

• The worksheet object has a UsedRange property,
  which is the range from the top left to the
  bottom right of cells that have been used on the
  worksheet.
• Results example

Sub used() Dim mycell As Range For Each mycell In ActiveSheet.UsedRange mycell.Interior.Color vbGreen Next mycell End Sub
16
Copy and Paste Example
Sub copytest() Range("C10").Copy Range("C1").Select ActiveSheet.Paste Application.CutCopyMode False End Sub
Sub copytest2() Range("C10").Copy Range("C1") End Sub
Sub copytest2a() Range("C10").Copy destination Range("C1") End Sub
17
Copy and Paste Example

• The following macro copies a cell from one
  workbook to another
• it can be made clearer using object variables

Sub copyrange() Workbooks("new.xls").Worksheets("sheet2").Range("A4"). _ Copy Workbooks("shapes.xls").Worksheets("Sheet1").Range("A1") End Sub
18
Copy and Paste Example
Sub copyrange2() Dim r1 As Range Dim r2 As Range Set r1 Workbooks("new.xls").Worksheets("sheet2").Range("A4") Set r2 Workbooks("shapes.xls").Worksheets("sheet1").Range("A1") r1.Copy r2 End Sub
19
Worksheets collection
Sub Newsheets() Dim n As Integer n InputBox("How many sheets do you want?") ActiveWorkbook.Worksheets.Add Count n End Sub
Sub AddSheetAfter() Worksheets.Add ActiveSheet.Move afterWorksheets(Worksheets.Count) End Sub
Sub AddSheetAtFront() Worksheets.Add ActiveSheet.Move before 'complete the argument End Sub
20
Worksheets Collection
Sub AddSheetWithName() Dim newSheetName As String newSheetName ActiveCell.Value Worksheets.Add.Name newSheetName End Sub
Sub AddSheetDaily() Dim newSheetName As String newSheetName Format(Now, "dd_mm") Worksheets.Add.Name newSheetName End Sub
Sub AddSheetAnyMinute() Dim newSheetName As String newSheetName Format(Now, "dd_mm hh_mm") Worksheets.Add.Name newSheetName End Sub
21
Exercises

• Add some blank sheets and some chart sheets to
  your week5 workbook
• Write a sub that returns a message box showing
  the number of worksheets and the number of sheets
  in the active workbook.
•  
• Write a macro to copy the team names from the
  results sheet to a new sheet , "AutumnResults"

22
Assessment 2

• You have 3 weeks to complete this task
• The aim is to provide you with a useful basis for
  future tasks, so you may adapt it to suit your
  own circumstances (as long as you explain changes
  in the comments). When complete, e-mail it to me.
  Name the file Ass2 your name
• Create a macro that, if saved to your personal
  macro workbook would be able to quickly format a
  series of worksheets and insert formulas etc.

23
Assessment 2

• Look at the workbook Jan 05
• It has a series of weekly sheets that will hold
  sales data for a number of products and stores
• The headings are formatted and there are formulae
  in place to calculate totals
• (There is a final sheet with formulas for
  displaying the weekly totals) - extension

24
Assessment 2

• The user should be able to open a new workbook,
  run your macro input the number of required
  products and stores and the month/ year.

25
Assessment 2

• You can extend this idea to create worksheets
  that would be more useful to you
• You might want to extend the idea of the summary
  sheet for example can you use a formula to show
  the product which sold the most? Can you view
  each weeks totals side by side

26
Assessment 2

• Remember that you can use the help files,
  textbooks, recorded macros and the internet for
  tracking down elusive code
• Formulas in this case, as you want to run a
  macro once and then use the workbook, use
  Formulas placed in cells rather than values
  calculated in the code

27
Next Week

• Do Loops