1 / 21

Excel User Functions

Excel User Functions. Writing your own Excel user function. Excel provides over 400 functions to users But sometimes it would be very convenient to have a function which is not provided

wes
Download Presentation

Excel User Functions

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. Excel User Functions

  2. Writing your own Excel user function • Excel provides over 400 functions to users • But sometimes it would be very convenient to have a function which is not provided • In our example (in workbook UserFunctionDemo) , we have a function that finds the middle value of three numbers • The first step is to create a module for the function

  3. Mac vs Windows • The process on the Mac is almost exactly the same • I’ve inserted some slides at the end to show how it looks on the Mac, and made a separate video • The one difference I’ve found is that the user functions you write don’t show up in the function list the way they do on Windows

  4. A User Function goes in a Module Instead of a UserForm, insert a Module

  5. Change the Module Name and Type the Code Use the Name property in the property box

  6. The name of my function shows up in the list of completions provided by Excel

  7. Here you can see it in action…

  8. To Add A Description… • Users find it helpful to see a description of what the function does • To provide one, click the macros icon (continued…)

  9. Type your macro name at the top (it’s not there because it’s not a workbook macro)

  10. Click the Options button…

  11. Type a helpful description and click OK If you give your macro a shortcut key, make sure it is not the same key used for something else really useful like control X, V, C or similar

  12. Another nice example • I got this one from the website exceltip.com • It was contributed by Martin Green, an Excel consultant • The idea: If you give Excel’s Weekday function a date, it will give you a number from 1 to 7 representing the day. You want to have a macro return a day name instead of a number • This is also implemented in workbook UserFunctionDemo

  13. Here’s the code (most of it) FunctionDayName(inputDateAs Date) As String DimdayNumberAs Integer 'we know this number is always small dayNumber = Weekday(inputDate, vbSunday) '*** Use Select Case to choose the dayname based on the number Select Case dayNumber Case 1 DayName = "Sunday" Case 2 DayName = "Monday" … Case 7 DayName = "Saturday" End Select End Function

  14. A few comments… • Giving the type of the value returned, as we did here, seems to be optional, even with Option Explicit in force FunctionDayName(inputDateAs Date) As String • The definition ends with End Function instead of End Sub, as it would with a sub procedure • This is a nice example of where the Select Case conditional gives clear, readable code • Weekday is an Excel function; the second argument is a constant that tells Excel we want 1 to represent Sunday

  15. Example for DayName

  16. How it looks on Mac…

  17. Go into the editor and choose Insert Module

  18. Rename the Module using the Properties window I named it MyFunction

  19. Type in the code and save

  20. In the Developer Tab, choose Macros… Type the macro name and choose Options, then type the description

  21. Go to Excel and Use Your Function! I used my function Middle in a formula for cell A4.

More Related