Learn EXCEL VBA in easy lessons


Télécharger Learn EXCEL VBA in easy lessons

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


Learn EXCEL VBA in easy lessons

...

User Defined Functions (UDF's)

A user defined function can be used when the built in Excel VBA functions do not meet the user’s requirements. The user defined function can then be used in formulas in the same way as a built in Excel function is utilised. User defined functions are limited to doing just calculations that result in a single return of a value.

The syntax of a user-defined function is as follows:

Function NameofFunction([Optional] Argument1 [As Type], _

[Optional] Argument2 [As Type], _

... [Optional] ArgumentN [As Type] ) [As Type]

Statements here ...

....

NameofFunction = Value being returned

End Function

NameofFunction The name of the function.

Arguments The arguments of the function.

If an argument is to be optional, enter the word Optional before the name of the argument.

The As Type option allows you to specify the data type for the return value.

Statements The various lines of code.

NameofFunction=Value Name is the name used in the first line of the function.

Expression is the return value of the function.

Note: The square brackets wrapped around a keyword in the above syntax denotes as optional and can be left out altogether.

Creating a User Defined Function

The following is a simple function example to convert Kilometers recorded into Miles.

  1. From the Developer tab on the Ribbon Bar, click the Visual Basic icon.
  2. Click on the Insert menu and select Module.
  3. Enter the following code:

Function ConvertToMiles(KM)

ConvertToMiles=KM / 1.6

End Function

  1. Back in the Excel spreadsheet, click on the Formula tab on the Ribbon Bar and click the Insert Function icon.
  2. From the list of Categories, select User Defined.
  3. Select ConvertToMiles and click on OK.
  4. Enter the cell reference of the Kilometer value you wish to convert into miles, into the KM field and click on OK.

Using built-in functions

It is possible to use built in Excel functions within a user defined function.

The syntax used for built in Excel Functions is as follows:

Application.NameofFunction(Arguments Required)

An example which incorporates the Excel Round function to the above user defined function

(ConvertToMiles).

Function ConverToMiles(KM)

ConvertToMiles = Applications.Round(KM / 1.6, 0)

End Function

The above amended code rounds the resulting returning value to zero decimal places using the standard Excel built-in Round function.

Using The Optional Argument

The Optional keyword preceding the argument name flags the argument as an optional parameter to the function call. A lot of built-in Excel functions have optional arguments which always follow on from the mandatory arguments listed in a function and can therefore be omitted defaulting to a value the function procedure knows how to handle if left out.

This makes function more flexible and can give different returning values (answers) and/or change the behaviour of how the function will run. Think of the VLOOKUP function in Excel, see it's syntax below:

= VLookUp ( Value , Range , Offset Column [ , Type ] )

The last argument (wrapped in square brackets) is optional and always appears after all mandatory arguments (3 in this example) which can be omitted and still work. The optional argument is a value of either True or False which defaults to True if omitted and simply changes the way how this function will calculate.

An example - following on from the above code snippet above, I want a second argument (as optional) which allows the user to choose a positive whole number (Byte data type) as its value to represent the number of decimal places to pass into the calculation. If omitted, it defaults to 0 decimal places round to the nearest whole number:

Function ConvertToMiles(KM, Optional DecPlaces As Byte)

If DecPlaces < 0 Then

ConvertToMiles = KM / 1.6

Else

ConvertToMiles = Application.Round(KM / 1.6, DecPlaces)

End If

End Function

The user can now either omit the second argument (cell B2), add a value of 0 to represent no decimal places (cell B3) or add a positive number to pass into the Excel Round function (cell B4).

Using the As Type option

Optionally, the As Type keywords can be included to define a certain data type the argument and/or the function is controlled. If omitted it will default to Variant (any data type it inherits) and can be open to abuse and more importantly errors. You define a data type (see Variables & constants for more information) for each argument in the function and for the function's returning value too. If left out, you will need to add more code to handle different data input scenarios.



Let's take a look at what happens if the last above example function is abused. In cell B3, setting the optional second argument to a String value "ABC" causes the #VALUE! error (a non numeric data input). In cell B4, setting the optional second argument to a negative number causes another error #NUM! even though it's a number but the argument data type Byte only accepts positive numbers between 0 and 255 as its range. The whole function is also expected to return a number which can be a larger than 255 and we therefore could apply the Integer as it's returning data type.

Function ConvertToMiles(KM, Optional DecPlaces As Byte) As Integer

If DecPlaces < 0 Then

ConvertToMiles = KM / 1.6

Else

ConvertToMiles = Application.Round(KM / 1.6, DecPlaces)

End If

End Function

Notice I have left out the argument KM data type which defaults to Variant. Personally, I prefer to test for a data type in the code itself when the user or system passes a value to calculate.

An example:

Function ConvertToMiles(KM, Optional DecPlaces As Byte) As Integer

If IsNumeric(KM) Then

If DecPlaces < 0 Then

ConvertToMiles = KM / 1.6

Else

ConvertToMiles = Application.Round(KM / 1.6, DecPlaces)

End If

Else

ConvertToMiles = 0 'If it fails return a 0

End If

End Function

I have tested to see if KM argument is a number by using the IsNumeric VB function.

All user defined functions can be called in Excel (as explained above) or into a calling Sub procedure like a VB or Excel function.

Event Handling

An Event is something that happens in a program such as:

Opening or closing a workbook

Saving a workbook

Activating or deactivating a worksheet or window

Pressing a key or key combinations

Entering/Editing data in the worksheet

Clicking the mouse on a control/object

Double clicking on a cell

Data in a chart is updated

Recalculating the worksheet

A particular time of day occurs

You can therefore run a procedure automatically when a certain Event in Excel occurs.

There are different objects (and therefore different levels) when Excel automatically triggers a procedure as the system is constantly listening for the event to occur.

Workbook Events

Open Event

The most common type of Open Event is Workbook_Open. This procedure is executed when the workbook is opened and is often used for the tasks such as:

Displaying a welcome message

Opening other workbooks

Setting up custom menus and toolbars

Activating a particular sheet or cell

Example:

Every time the user opens the workbook, they are greeted with a message box displaying the day of the week. If it is a Friday, a message box will remind the user to submit their timesheet.

  1. Open the required workbook.
  2. Switch to the Visual Basic Editor.
  3. Double click on ThisWorkbook from within the Project Explorer.
  4. Click on the Object drop down list and select Workbook
  5. Enter the following between the signature Private Sub Workbook_Open() and End Sub keywords:

Private Sub Workbook_Open()

MsgBox "Today is " & WeekdayName(Weekday(Now), False, vbSunday)

If Weekday(Now) = vbFriday Then

MsgBox "Don't forget to submit your timesheet"

End If

End Sub

Note: Private means that the procedure won’t appear in the Run Procedure dialog box (i.e. Macros dialog). See

Scope & Visibility in Variables & Constants for information.

Workbook Activate Event

The procedure is executed whenever the workbook is activated (gets the focus).

Example:

Call the signature Private Sub Workbook_Activate() using the same methods as previously explained above.

Enter the following code:

Private Sub Workbook_Activate()

ActiveWindow.WindowState = xlMaximized

End Sub

Now the window will always maximise when the workbook gets the focus.

Note: Deleting an event (the signature) will not harm the system as it is re-generated each time you call one of the pre-defined signatures.

Example:



Using the Private Sub Workbook_SheetActivate(ByVal Sh As Object)signature is triggered across any worksheet in the active workbook.

Enter the following code:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Range("A1").Value = Date 'Enters the current date in A1

Range("A2").Select 'Position the cursor in A2

End Sub

The 'Sh' argument can also be used to refer to which worksheet is being called should you wish to control the index or name of a particular worksheet or group of worksheets.

By including a code line: If Sh.Name = “Sheet3” Then... it will handle the logic and control flow for 'Sheet3'.

Worksheet Events

Worksheet Activate Event

Within a workbook you also have separate nodes for each added worksheet chart sheet which contain a private (local) module over an above standard modules in a VBA project.

Example:

Every time the user clicks on 'Sheet1' if the first cell (A1) is empty then prompt the user with an

InputBox function to enter a title.

Private Sub Worksheet_Activate()

If Trim(Range("A1").value) = Empty Then

Range("A1").Value = Trim(InputBox("Enter title:"))

Range("A1").EntireColumn.AutoFit

End If

End Sub

Note: If there are events at both the worksheet and workbook level which point to the same object (worksheet), then it's the worksheet level will run first followed by the workbook event.

Other Events

There are other ways to get Excel to trigger a macro using other events from other objects or controls.

It is possible to attach procedures to the ActiveX Controls so that whenever the user clicks onto a control, the procedure will run.

Example:

When the user clicks on the Command Button, a message box will appear.

  1. From Excel, click on the Developer tab (Ribbon Bar), select Insert icon and choose Button icon from the Form Control section.
  2. Draw the Command Button onto the spreadsheet.
  3. The Assign Macro dialog box appears, Click the New... button.
  4. Enter the following code:

Sub Button37_Click()

MsgBox "Button click event!"

End Sub

Any control drawn on a worksheet or user form will have pre-defined events that can be coded to respond by the system.

How do you think features like conditional formatting and data validation work in a worksheet when set in Excel? When the user enters a value in a cell, the Change event is triggered:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("A2") Then Range("A2").Font.Bold = True

End Sub

Target is the argument to test which cell address is being changed.

Error Handling

No matter how thorough you are when writing code, errors can and will happen.

There are steps that developers can take to help reduce unwanted errors and this is considered just as important as the actual process of the procedure.

Before understanding and applying error-handling routines, planning to avoid errors should be undertaken.

Design the procedure’s process electronically or on paper – flow chart and paper test.

Creating smaller portions of code – snippets to be called and re-used

Using the Option Explicit statement – declaring your variables officially.

Syntax checking – user defined commands and functions.

Comments – remarking your code at various points.

Testing application – functional and usability.

Note: Some of the above points are methodologies which are outside the scope of this reference guide.

There are three different types of errors:

  1. Design Time Errors
  2. Run Time Errors
  3. Logical Errors

The order of the above progressively is harder to find leaving the last item the most challenging!

Design Time Errors

The simplest form of error and often caused by typing (typo's) mistakes.

When typing a known keyword or statement, VBA will turn the text to red (default colour) and if the option is enabled, provide a prompt:

To switch off the above prompt, go to Tools select Options… and deselect Auto Syntax Check option.

The routine will instantly cause a run time error if not corrected at the design time and must but resolved before macros can run.

Run Time Errors

When executing code, no matter how thorough the debugging process has been, code may encounter errors while running.

There is only one way of doing this - On Error GoTo instruction. It is not a very sophisticated function, but it allows the flow of the code to continue and also where applicable, prevent infinite loops (when the computer keeps on calculating never coming to an end).

Three variations are available:

  1. On Error GoTo LabelName
  2. On Error Resume Next
  3. On Error GoTo 0



25