Excel advanced
This presentation is the property of its rightful owner.
Sponsored Links
1 / 128

Excel Advanced PowerPoint PPT Presentation


  • 99 Views
  • Uploaded on
  • Presentation posted in: General

Excel Advanced. Ann Maes & Frederik Gailly Department of Management information Faculty of Economics and Business Administration. Information. Outline, Program, Downloads: http://allserv.ugent.be/~fgailly/ExcelAdvanced Contact: Ann Maes ( [email protected] )

Download Presentation

Excel Advanced

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Excel advanced

Excel Advanced

Ann Maes & Frederik Gailly

Department of Management information

Faculty of Economics and Business Administration


Information

Information

  • Outline, Program, Downloads:http://allserv.ugent.be/~fgailly/ExcelAdvanced

  • Contact:

    • Ann Maes ([email protected])

    • Frederik Gailly ([email protected])


Outline session 1

Outline: Session 1

  • Pivot tables and graphs

  • Data tables

  • Scenario management

  • Goal seek

  • Solver

  • Importing Financial data from the Web


Outline session 2

Outline: Session 2

  • Macro’s

  • Explore VBA-objects

  • VBA selection and repetition

  • VBA buttons and events

  • VBA form elements


Pivot tables

Pivot Tables

  • Transform large amounts of data from a table or database into an organized summary report

  • Ability to rotate and reorganize the information via “drag-and drop”

  • Ability to filter and sort data as desired


Ex sales bookstore

Ex. Sales bookstore

Sales per category, per channel

and per year, per trimester?


Creating a pivot table

Creating a Pivot Table

  • Data > Pivot Table and Pivot Chart Report

  • Three simple steps:

    • Step 1: Data Location

      • Where is the data to analyze?

      • Pivot table or pivot chart?

    • Step 2: Data Source

      • Select range of cells

      • Select file using Browse

    • Step 3: Creating the Pivot Table

      • Location

      • Layout

      • Options


Step 1 data location

Step 1: Data location

  • The data is in a Microsoft Excel list

  • We will create a Pivot Table


Step 2 data source

Step 2: Data Source


Step 3 creating the pivot table

Step 3: Creating the Pivot table

  • Step 3

    • The location will be as a new worksheet

    • Must now organize Layout


Layout

Layout

Field buttons


Terminology

Terminology

  • Fields are categories of data (these may usually be row or column headings in a table)

  • Row Fields show each value, or item, in the field as a row

  • Column Fields show each value as a column heading

  • Data Area is the main area of the table where comparative values are shown

  • Grand Totals and Subtotals are sum calculations that appear at the end of relative rows or columns

  • Page Field is a larger category which can group all of the data in the table


Excel advanced

Pivot table template

Page field: creates a filter for your table

Data field: what information do you want to summarize?

Column and Row fields: define how data will be displayed in table


The resulting pivot table

The resulting Pivot Table…


Pivot table page field

Pivot table Page Field

  • Operates like row and column fields but provides a third dimension to your data

  • Without necessarily viewing all its values at the same time (filter)

  • You can also choose to

    display pivot table pages

    on separate worksheets


Page field

Page Field


Exercise

Exercise

  • Show sales per category and channel for 2002 and 2003 in separate pivot tables

  • Show sales per category and channel for each trimester of 2003 in separate pivot tables.


Modifying pivot table

Modifying Pivot Table

  • Only selection of values of row/column/ page fields in pivot table

     Drop down list of variable

  • Change type of calculation (default Sum)  field settings

  • More than one data-variable in pivot table

  • Grouping data

     Group and show detail > Group


Calculated fields

Calculated Fields

  • Pivot Table Toolbar > Pivot Table > Formulas > Calculated Field to create a new field

  • Ex: Commission


Pivot charts

Pivot Charts

  • Pivot Table Toolbar > Pivot Charts icon

    • Chart is created from Pivot Table instead of initial data table

  • Data > Pivot Table and Pivot Chart Report

    • Step 1: Pivot Chart

    • Pivot Chart created from data table

  • Pivot Chart has same filtering options as Pivot Table


Template pivot chart

Template Pivot Chart


Ex show sold units per channel and per year

Ex. Show sold units per channel and per year

! If you edit your PivotChart, changes will be made to Pivot Table and vice versa!!


What if analysis

What-if analysis

“Allows you to change certain conditions in your worksheet to see how these changes affect the result of various spreadsheet calculations”

  • a One and Two-Input Data table

  • Scenario Manager (scenario summary)

  • Goal Seek

  • Utilize Solver


Sensitivity analysis data table

Sensitivity Analysis: Data Table

  • To track how small changes in inputs affect the results of formulas in your model that are dependent on those inputs.

  • Two varieties:

    one-variable data table

    two-variable data table


One way data table

One-Way Data Table

One or more formula

List of values for input variable

Data Table Matrix with values


One way data table1

One-Way Data Table

  • Enter references to formulas to compute across the top (or side)

  • Enter input data,

  • Select the cells comprising the table and use Data | Table…

  • Specify which input value

    is varied


Example

Example

  • Calculate the monthly payment for a loan of 12400 EUR of 4 years when you consider a constant interest rate of 11%

  • What would the monthly payment be when the interest rate varies (10%; 10,5%; 11%; 11,5%; 12%;12,5%;13)


Result

Result…


What if

What if…

Not only uncertainty about interest rate but also about amount to loan.

  • Calculate monthly payment for loan at different interest rates en for several amounts (10000, 11000, 12000, 13000, 14000)

  • Two-way data table


Two way data table

Two-Way Data Table

List of values for other input variable

Formula or reference to formula

List of values for input variable

Data Table Matrix with values


Two way data table1

Two-Way Data Table

  • Enter reference to formula in top left-hand corner of table

  • Enter values for input data, in left column and top row

  • Select cells in table, and use Data | Table…

  • Specify which input values

    are specified in the table


Two way data table2

Two-way data table


Excel advanced

Scenario management

  • To perform what-if analyses with more than two input variables  use scenarios, which are:

    • A set of values for multiple cells that Excel can put into a worksheet

    • Created based on existing spreadsheets in Excel

  • Use the Scenario Manager to set up and view different scenarios.

  • As you view each scenario, Excel uses the values in the scenario as input to calculate the results.


Ex scenario management

Ex. Scenario Management


Excel advanced

Scenario Management: define several scenarios

Use Tools > Scenarios … menu optionAdd scenarios (with Add… button)


Adding additional scenarios

Adding additional Scenarios


Excel advanced

View and edit scenarios

  • Scenarios defined you can view one by selecting the name of the scenario you want to see in the Scenario Manager dialog box and click Show and then Close.

  • Excel will display the original spreadsheet, with the values from the scenario you chose.

  • You can edit your scenarios from the Scenario Manager

    • Select the scenario you want to edit, and click Edit

    • This will bring up a dialog box in which you can change any of the input values

    • You can then display the spreadsheet with the values from the edited scenario


Excel advanced

Scenario summary report

  • display a summary of the results from all of the scenarios you have created.


Excel advanced

Scenario Summary Report

The Scenario Summary report shows you how your performance measure and your decision varyfor scenarios where multiple input variables are changing.


Excel advanced

What If vs If What

  • Data Table and Scenario Analysis asked the question “WHAT IF …” ?

  • We might also want to ask the question “IF … WHAT” ?

    • IF our profit is to be $100, WHAT must our sales be?For this question, we can use Goal Seeking

    • IF our profit is to be maximized, WHAT must be done?For this question, which is more complex, we can use Solver


Goal seeking

Goal seeking

  • Use this when you want to find a specific result for a cell by adjusting the value of one other input cell.

  • Use Tools > Goal Seek…

  • Under Tools > Options… > Calculations you can change the maximum iterations, the precision, etc.

  • At the end you can either ignore or accept the solution Excel suggests.


Example car payment

Example Car Payment


Excel advanced

Tools > Goal Seek

If the monthly payment may not exceed 200 what should the duration of the loan be….

Output cell address

Target level sought

Input to vary


Result1

Result…


Excel advanced

Optimization Analysis: Solver

  • Finds set of decision variables that achieves best possible value of an output

  • Answers questions such as:

    • How should we allocate our budget to maximize profit?

    • How much inventory should we stock of each type of product, given constraints on shelf size and budget?


Excel solver

Excel Solver

  • to solve a mathematical model which has been entered into an Excel spreadsheet

  • This mathematical model can be either

    • Linear Programming problem = there is a linear relationship among all constraints and the objectivefunction

    • Integer Programming problem = decisionvariables can only take integer values in a given range (these integer values can also be boolean = 0 or 1 only)


The mathematical model

The Mathematical Model

  • Decision Variables = variables assigned to a quantity or response that must be determined in the problem

  • Objective Function = equation which states the goal of the model

    • Maximize

    • Minimize

  • Constraints = equations which state limitations of the problem

  • To solve the model, each constraint must be considered simultaneously in conjunction with the objectivefunction


Tools solver

Tools > Solver


The solver steps

The Solver Steps

  • Step 1: Read and Interpret the Problem

    • Step 1.1: determine the decision variables

    • Step 1.2: state the objective function

    • Step 1.3: state any constraints

  • Step 2: Prepare the Spreadsheet

    • Step 2.1: Enter the decision variables

    • Step 2.2: Enter the constraints

    • Step 2. 3: Enter the objective function

  • Step 3: Solve the model with the Solver

    • Step 3.1: Set the Target Cell and choose Min or Max

    • Step 3.2: Select Changing Cells

    • Step 3.3: Add Constraints

    • Step 3.4: Set SolverOptions

    • Step 3.5: Solve and review Results


Ex capital budgeting

Ex. Capital Budgeting

  • A company has six different opportunities to invest money. Each opportunity requires a certain investment over a period of 6 years or less. The Company wants to invest in those opportunities that maximize the combined Net Present Value (NPV). It also has an investment budget that has to be met for each year. In each year there is only a limited amount of money available. All amounts are give in millions of dollars. Interest rate is 5%.


Step 1 read and interpret the problem

Step 1: read and interpret the problem

  • Decision Variables

    • Investment percentages in each opportunity

  • Objective Function

    • Maximize combined NPV

  • Constraints

    • Investment percentages < 1

    • Cash flows < investment budget (Year 1…6)


Step 2 prepare the spreadsheet

The spreadsheet should have each part of the model clearly entered

Step 2: Prepare the Spreadsheet


Step 3 solve the model

The Solver parameters can now be set according to the cell references with the appropriate model parts

Step 3: solve the model


Step 3 continued

Step 3 (continued)

  • SolverOptions should also now be set


Results

Results


Solver reports

Solver Reports

  • Answer Report = lists the target cell and the changing cells with their original and final values, constraints, and information about the constraints.

  • Sensitivity Report = shows how sensitive the solution is to small changes in the objective function formula or the constraints. For linear models, the report includes reduced costs, shadow prices, objective coefficient (with allowable increase and decrease), and constraint right-hand side ranges.

  • Limits Report = lists the target cell and the changing cells with their respective values, lower and upper limits, and target values. The lower limit is the smallest value that the changing cell can take while holding all other changing cells fixed and still satisfying the constraints. The upper limit is the greatest value.


Quick review

Quick Review

  • Pivot tables and graphs

  • Data tables

  • Scenario management

  • Goal seek

  • Solver


Importing financial data from the web

Importing financial data from the web

  • Web queries can assist you in inserting refreshable data from the Web into your worksheets.

  • You can now navigate to any Web page while setting up a Web query, the same way as you would navigate to that page in your browser.

  • Once you are at the page, you can select one or more tables by clicking the icon images automatically added to the page by Excel.


Importing financial data from the web1

Data > Import External Data >New Web Query.

Importing financial data from the web


Basic steps

Basic steps

  • Click the arrow next to the tables you want to import ( arrow checkmark)

  • Web queries are automatically saved with your workbook. If you want to save the query so that it can be run in other workbooks, click Save Query (.iqy extension.)

  • To set formatting and import options for how data is returned, click Options

  • Import Data

  • Choose location data


Result web query

Result web query


Excel advanced session 2

Excel Advanced: Session 2

Visual Basic For Applications


Contents

Contents

  • Macro’s

  • Explore VBA-objects

  • VBA selection and repetition

  • VBA buttons and events

  • VBA form elements


1 macro s

1.Macro’s


Macro s

Macro’s

  • Visual Basic toolbar

    • View – Toolbars – Visual Basic

Play Macro

Record Macro

Security Settings

Visual Basic Editor


Macro s1

Macro’s

  • Improve functionality

  • Yourfirst macro:

    • Open budget.xls

    • Select D7:F8

    • Record Macro

    • Name: CurrencyFormat

    • Cell Properties

      • Valuta

      • Decimals: 0

    • Stop record Macro

Name Macro


Run macro s

Run Macro’s

  • Select cells

  • Run Macro

  • Select CurrencyFormat

  • Run


Edit macro in vb editor

Edit Macro in VB Editor

  • Select run Macro

  • Select Macro

  • Edit


Macro s in vb editor

Macro’s in VB editor

  • Fixed syntax:

    Sub name_macro()

    ….

    End Sub

  • ‘  Remarks

  • Between sub and end sub:Body Macrocontains one or more statements


Sign your macro s

Sign your macro’s

  • !!! Vlerick computers only accept signed macro’s

  • How to sign your macro’s

    • Create a signature

    • Add your signature to the workbook


Create a signature

Create a signature

  • Programs > Microsoft Office Tools > Digital Certificate for VBA projects


Add your signature to the workbook

Add your signature to the workbook

  • Microsoft VB EditorTools > Digital Signature


Exercise macro

Exercise Macro

  • Create a macro that

    • merges a selection of cells

    • vertical aligns the text.

    • Rotates the text 90°


Solution macro

Solution Macro

Sub VerticalMerge()

'

' VerticalMerge Macro

' Macro recorded 21/09/2004 by fgailly

With Selection

.HorizontalAlignment = xlGeneral

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 90

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = True

End With

End Sub


Simplify solution

Simplify Solution

Sub VerticalMerge()

'

' VerticalMerge Macro

' Macro recorded 21/09/2004 by fgailly

'

With Selection

.VerticalAlignment = xlCenter

.Orientation = 90

.MergeCells = True

End With

End Sub


Methods in macro s

Methods in Macro’s

  • Until now:Record macro’s that change the properties of a selection

  • Next Step:Record methods in a macro.

  • Example: Macro to transpose formulas into values


Solution

Solution

Sub ConvertIntoValues()

'

' ConvertIntoValues Macro

' Macro recorded 21/09/2004 by fgailly

'

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

End Sub


Summary

Summary

  • 2 kinds of statements:

    • Statements that change properties:With this you assign a new value to this property.Ex: Selection.VerticalAlignment = xlCenter

    • Methods:You don’t assign a new value. You are doing something. Methods can have argumentsEx: Selection.PasteSpecial _ Paste:=xlPasteValues, _Operation:=xlNone, _


Execute complex tasks with macro s

Execute Complex tasks with macro’s

  • Import File:

    • Open file

    • Wizard import text

    • Move sheet to workbook

    • Delete row


Importfile macro

ImportFile Macro

Sub ImportFile()

Workbooks.OpenText Filename:="C:\Excel VBA Oefenbestanden\Nov2002.txt", _

Origin:=xlMSDOS, StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array( _

Array(0, 1), Array(8, 1), Array(20, 1), Array(27, 1), Array(41, 1), Array(49, 1)), _

TrailingMinusNumbers:=True

ActiveWindow.WindowState = xlNormal

With ActiveWindow

.Top = -9.5

.Left = 3.25

End With

Sheets("Nov2002").Select

Sheets("Nov2002").Move Before:=Workbooks("hoofdstuk2.xls").Sheets(1)

Rows("2:2").Select

Selection.Delete Shift:=xlUp

End Sub


Improve importfile macro

Improve importfile macro

  • Users must be able to select the txt-file:Application.GetOpenFileName(“…, ….”)

  • Remove unnecessary statements

  • Macro’s must be used for all the months of the yearSheets(“nov2002”).Move  ActiveSheet.Move


1 select file

1.Select File

myfile = Application.GetOpenFilename("txt-files,*.txt")

Workbooks.OpenText Filename:=myfile, _

Origin:=xlMSDOS, StartRow:=4, _ DataType:=xlFixedWidth, FieldInfo:=Array( _

Array(0, 1), Array(8, 1), Array(20, 1), _ Array(27, 1), Array(41, 1), Array(49, 1)), _

TrailingMinusNumbers:=True


2 remove unnecessary statements

2. Remove unnecessary statements

Sub ImportFile()

myfile = Application.GetOpenFilename("txt-files,*.txt")

Workbooks.OpenText Filename:=myfile, _

Origin:=xlMSDOS, StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array( _

Array(0, 1), Array(8, 1), Array(20, 1), Array(27, 1), Array(41, 1), Array(49, 1)), _

TrailingMinusNumbers:=True

ActiveWindow.WindowState = xlNormal

With ActiveWindow

.Top = -9.5

.Left = 3.25

End With

Sheets("Nov2002").Select

Sheets("Nov2002").Move Before:=Workbooks("hoofdstuk2.xls").Sheets(1)

Rows("2:2").Select

Selection.Delete Shift:=xlUp

End Sub


3 all the months of the year

3.all the months of the year

Sub ImportFile()

myfile = Application.GetOpenFilename("txt-files,*.txt")

Workbooks.OpenText Filename:=myfile, _

Origin:=xlMSDOS, StartRow:=4, DataType:=xlFixedWidth, _ FieldInfo:=Array( _

Array(0, 1), Array(8, 1), Array(20, 1), Array(27, 1), _

Array(41, 1), Array(49, 1)), _

TrailingMinusNumbers:=True

ActiveSheet.Move _

Before:=Workbooks("hoofdstuk2.xls").Sheets(1)

Rows("2:2").Select

Selection.Delete Shift:=xlUp

End Sub


Further exercises

Further exercises

  • Add the missing labels

  • Add column with dates

  • Add month to database

  • Remove worksheet

    You can try this at home and send your solution to [email protected]


Explore vba objects

Explore VBA-objects

Workbooks, Worksheets, Range, Cells


Objects in excel

Objects in excel

  • Objects exists in collections

    • Workbook  object

    • Workbook budget.xls  instance of an object

  • Objects have properties

    • Author: property of a workbook

    • Height: property of a cell

    • Name: property of a worksheet

  • Objects have methods

  • Collection of objects have methods


Immediate window

Immediate Window

  • We will use the immediate window to explore the VBA objects.

  • View > immediate window


Workbooks

Add a workbook to the collection

Workbooks.add

Count workbooks

Workbooks.count

Close all workbooks

Workbooks.close

Refer to a workbook

Workbooks.Item(1)

Workbooks(“Book1”)

Name of a workbook

Workbooks.Item(1).name

Close a workbook

Workbooks.Item(1).close

ActiveWorkBook.close

Activate Workbook

Workbooks(“Map14”).activate

Workbooks


Worksheets

Worksheets

  • Add a worksheet to the collection

    • Worksheets.add.name = “Nieuw blad”

  • Delete a worksheet

    • Worksheets.Item(1).delete

  • Rename worksheet

    • Worksheets(1).name = “Test”

  • Activate worksheet

    • Worksheet(“Test”).activate

  • Copy worksheets

    • Worksheets(“Test”).Copy

    • Worksheets(“Test”).Copy Before:=Worksheet(1)

  • Select worksheets

    • One: Worksheets(2).select

    • More than One: Worksheets(Array(1,3,4)).select


Range

Range

  • Point to a range with an address

    • Range("AA190").Select

    • Range("A12:B34").Select

  • Special Range Object  Cells

    • Cells.Select

    • Cells.Item(5).select

    • Cells.Item(4,2).select

  • Special Range Object  Colums / Rows

    • Columns(3).Select

    • Columns("D").Select

    • Rows("3:14").select


Currentregion entirecolums entirerow

CurrentRegion, EntireColums, EntireRow

  • Point to a region based on the current selection

    • ActiveCell.Currentregion.Select

    • ActiveCell.EntireColumn.Select

    • Selection.EntireRow.Select


Offset m n

OffSet(m,n)

  • ActiveCell.offset(1,0).Select Go one row down

  • ActiveCell.Offset(2,2).Select Go two rows down Go two columns to the right

  • ActiveCell.Offset(-1,-1).Select Go one row up Go one column to the left


Add a value to range object

Add a value to range-object

  • Selection.value = 100

  • ActiveCell.Offset(-1).value = 0

  • Selection.Formula = 100

  • ActiveCell.Offset(-1).Formula = 100


R1c1 a1 notation

R1C1 A1 notation

  • A1 notation

    • A: column

    • 1: row

  • R1C1 notation

    • R1: row 1

    • C1: column 1

    • More possibilities:

      • R[-2]C  same columns, 2 rows higher

      • R[1]C5column 5, 1 row lower


Add a formula to range

Add a formula to range

  • Activecell.Formula = "=A1 * B2“

  • Activecell.FormulaR1C1= "=R[-2]C - R[-1]C"


Excel advanced

VBA

Selection and repetition


Conditional statements

Conditional Statements

If …….. Then….Elseif …. Then….Else

….

End if


Example 1

Example 1

  • Sub GoLeft()ActiveCell.Offset(0, -1).SelectEnd Sub

  • !!! In cell A1: Error

  • Resolution:Sub GoLeft()If ActiveCell.Column <> 1 Then ActiveCell.Offset(0, -1).SelectEnd IfEnd Sub


Example 2

Example 2

Sub GoRight()If ActiveCell.Column < 5 ThenActiveCell.Offset(0, 1).SelectElseCells(ActiveCell.Row + 1, 1).SelectEnd IfEnd Sub


Message boxes

Message Boxes

  • 2 types:

    • MsgBox “Text”  simple messages

    • MsgBox(“text”, Buttons, “Title”) User has to respond (ex. Press YES)

  • Examples:

    • Msgbox “Proceed with the macro”

    • Msgbox(“Proceed with the Macro”, vbYesNo, “Proceed”)


Inputbox

InputBox

  • User has to respond by typing a text and pressing OK

  • Example:answer = InPutBox(“Give the month”)


Example 3

Example 3

Sub TestInput()

Dim myDate As String

myDate = InputBox("Give the Date (mmm-jjjj)")

If myDate <> "" Then

If IsDate(myDate) Then

MsgBox "Continue Macro"

Else

MsgBox "Invalid Date"

End If

End If

End Sub


Example 3 bis

Example 3 bis

Sub TestInput()

Dim myDate As String

myDate = InputBox("Give the Date (mmm-jjjj)")

If myDate <> "" Then

If IsDate(myDate) Then

answer = MsgBox(“Continue?”, vbYesNo)

If answer = vbNo Then

Exit Sub

End If

Else

MsgBox "Invalid Date"

End If

End If

End Sub


Loops for each

Loops: For Each

  • For eachUsed to walk through a collection of objects

  • For Each In …….Next


Example 11

Example 1

Sub ProtectSheets()

Dim mySheet as WorksheetFor Each mySheet in Worksheets

mySheet.Select

mySheet. Protect “Password”, _

True, True, True

Next mySheet

End Sub


Example 21

Example 2

Sub unprotectSheets()

Dim mySheet As Worksheet

For Each mySheet In Worksheets

mySheet.Select

mySheet.Unprotect "Password"

Next mySheet

End Sub


Loops for

Loops: For

  • For-loopUsed to execute statements for a couple of times by using a counter

  • For i = 0 to n….Next i


Example1

Example

Sub ProtectSheets()

For i = 1 To Worksheets.Count

Worksheets(i).Protect "Password", _True, True, True

Next i

End Sub


Loops do until do while

Loops: Do Until / Do While

  • Do Until [condition]…Loop

  • Do While [condition]….Loop


Example 12

Example 1

Sub FilesEnhanced()

Dim myRow As Integer

Dim myFile As String

myRow = 1

myFile = Dir("C:\Documents and _Settings\fregai\Bureaublad\*.doc")

Do Until myFile = ""

Cells(myRow, 1) = myFile

myRow = myRow + 1

myFile = Dir

Loop

End Sub


Excel advanced

VBA

buttons and events


Using buttons in excel

Using buttons in excel

  • Toolbars / control toolbox

  • Select Button

  • Draw Button

  • Every toolbox element has properties:

    • Name

    • Caption


Using buttons in excel 2

Using buttons in excel (2)

  • Attach macro to button:

    • Create macro with special name:Sub NameButton_click()….End Sub

    • !!! Name Button is very important

    • Use Meaningful names


Example 13

Example 1

Sub ZoomIn_click()

Dim MyZoom As Integer

MyZoom = ActiveWindow.Zoom + 10

If MyZoom <= 400 Then

ActiveWindow.Zoom = MyZoom

End If

End Sub


Using buttons in excel 3

Using buttons in excel (3)

  • To test a button  exit design-mode

  • Click  eventCode will be executed when the event happens

  • Other Events:

    • MouseMove

    • ….


Worksheet workbook events

Worksheet & WorkBook events

  • Select Object & Select Event

  • Events:

    • Worksheets:

      • SelectionChange

      • Activate

      • Change

      • Deactivate

    • WorkBook:

      • Activate

      • Open


Example 14

Example 1

Private Sub Worksheet_SelectionChange(ByVal _ Target As Range)

If ActiveCell.Interior.Color = vbCyan Then

Selection.Interior.Color = vbYellow

Else

Selection.Interior.Color = vbCyan

End If

End Sub


Excel advanced

VBA

form-elements


Example2

Example

  • Create a loan calculator:

  • Formula: =PMT(C5/12;C6*12;C4)


Improved calculator

Improved Calculator

  • Limit number of years

  • Limit possible rates

  • Limit loan amounts

  • Protect Worksheet


Limit number of years

Limit number of years

  • Control Toolbox > Spin Button

  • Properties:

    • Max: 30

    • Min: 5

    • Linked Cell C6


Limit possible rates

Limit possible rates

  • Control Toolbox > Scroll Bar

  • Properties:

    • Max: 2000

    • SmallChange: 25

    • LargeChange: 200

    • LinkedCell: H5

  • Formula C5: = H5/10000


Limit loan amounts

Limit Loan amounts

  • Use a listbox

  • First  Create a list with possible values on an other worksheet:

  • Select List

  • Give a RangeName  AutoList


Limit loan amounts 2

Limit Loan amounts (2)

  • Control Toolbox > List Box

  • Properties:

    • LinkedCell: C2

    • ListFillRange: AutoList

    • ColomnCount: 2

    • BoundColumn


Protect worksheet

Protect WorkSheet

  • Create eventhandlers for Elements

    • List Box

      • Properties:

        • Name: lstAmount

        • LinkedCell : “”

      • Sub lstAmount_Change()Range(“C4”).Value = lstAmount.ValueEnd Sub

    • Scroll Bar

      • Properties

        • Name: scrRate

        • LinkedCell: “”

      • Private Sub scrRate_Change()Range("C5").Value = scrRate.Value / 10000End Sub

    • ….


Protect worksheet 2

Protect Worksheet (2)

VBA editor: In ThisWorkBook

  • Sub Workbook_Open()WorkSheets(“Lening”).Protect _UserInterfaceOnly:= TrueEnd Sub


The end

The End

Thank You

for your Attention


  • Login