Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing a Custom Function (Excel XP)

    Hi. I need some help getting started with creating a custom function in Excel. I'm very familiar with VBA (I write a ton of code for Word), but I can't seem to find any help on creating a custom spreadsheet function for Excel. I can write the code that manipulates the data in the way I want, but I don't know how to make it available to my Excel spreadsheets. (I know that, in Word, the concept is to create a template containing the subroutines or functions and put it in the start-up folder.)
    For what it's worth, the function I would like to create would be =ElapsedWeekdays(startdate, enddate), and I can get it to run in the VBA editor, but where do I go from there? Is it an add-in or a template? How will my other spreadsheets "see" the function? MS TechNet is virtually no help, as the examples seem to use menu options I don't even have. (E.g., my VB Editor does not have a File: New command!)
    Help, please!
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    Well, the main key is putting the code into a Module - under Insert on the VBE Main Menu. If you want the code available only for a specific Workbook, then insert it in the Workbook. If you want to distribute the code as an Add-In (sort of like a Global template), then instead of saving the Workbook as a Workbook, save it as an Add-In. Finally, if you want the code available to all your own workbooks only, then include that code in a file called Personal.xls. See this <!post=Star Post,118382>Star Post<!/post>. HTH
    Gre

  3. #3
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    Here is the problem as it stands now:
    I created the function and it does what I need it to do (i.e. the actual math part!)
    I saved the spreadsheet as Cocuments and SettingsusernameApplication DataMicrosoftAddInsesias.xla in Add-In format.
    I closed and restarted Excel. I went to Tools: Add-Ins... and browsed for it. It was then "checked" as an add in, and the function worked.
    BUT when I close Excel and restart, I go to Tools: Add-Ins..., it's still listed and a check is next to it but I can't use the function! I have to un-check the add-in and then re-check it for it to be "recognized."
    Am I missing something??
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    Check your Security settings under Tools|Macros|Security. You may also need to sign the code. If you are not familiar with this routine, here is a "boilerplate" walk-through. HTH

    You can sign your code - for which Microsoft have provided a simple option using SelfCert.exe in the C:Program FilesMicrosoft OfficeOffice directory. The certificate will then be available for signing through the Tools option on the VBE Worksheet Menu Bar. You will probably have to have the SelfCert certificate recognised by Windows as a Trusted Certificate. This procedure is not wholly intuitive. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Make sure you choose View Certificate first - as that is the first step in getting it properly registered - for which you need to choose the Details|Copy to File option. Finally, you will have to export it to a file from Control Panel|Internet Options|Content|Certificates|Personal|Export, and then reimport it to the Trusted Store using Control Panel|Internet Options|Content|Certificates|Trusted Publishers|Import.

    You do have to lower your default Macro security level, when first running the Workbook containing the signed code, to get the option to trust the signer.
    Gre

  5. #5
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    Macro security here at this company is already set to "Low." (We use Norton Antivirus.)
    Also, how do i open the add-in again? If I use File Open, I see nothing. It's as though I'm adding it to the add-ins collection, but it won't open for editing.
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Writing a Custom Function (Excel XP)

    If you go to VB Editor (ALt-F11) you should be able to edit the code. Addins are "very hidden" and will not show up in the windows unhide.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    The weirdness continues.
    I wrote my add-in. I put it in C:Program FilesMicrosoft OfficeOffice10XLStart. I started Excel. I used Tools: Add-ins... to "install" the add-in.
    I created a small spreadsheet to test the function. It worked fine.
    I exited Excel.
    I re-opened the test spreadsheet and got an error message saying "This workbook contains links to other data sources." I told it to update. The formulae then displayed the #NAME? error and the formula bar showed the formula as "='C:Program FilesMicrosoft OfficeOffice10XLStartesias.xla'!countweekdays(A3,B 3)."
    So I went to Tools: Add-ins... and the add-in WAS listed there and had a check next to it. I unchecked the add-in and the calculations reverted to their previous value. And, as before, if I go back to Tools: Add-ins... and re-check the add-in, it works OK again.
    Macro security is set to "Low" and other add-ins seem to work fine.
    The XLA file only contains a single simple function that takes two arguments and returns an integer, and no modules contain any declarations or anything.
    I MUST be missing something, but I have NO CLUE what it could be!
    Help!!
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    Don't put the addin in xlstart. As soon as an addin is checked, it will load anyway.

    Could you post your code here?

    BTW: the analysis toolpak addin already has a networkdays function, it might already fit your needs.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    My question is to all the "guru's" and by the way I have learned a lot from you all in the past. Maybe I understand the problem wrong, but why can he not create a cumtom button on the toolbar and asign the macro to it?
    Regards
    Kobus

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Writing a Custom Function (Excel XP)

    It is not a subroutine it is a function. You would be putting the function in a cell and not running a routine from the toolbar.

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    Sure, here is the code:

    <hr>Function CountWeekdays(startDate, endDate) As Integer
    Dim sDate As Date, eDate As Date
    Dim nDays As Integer, nWeeks As Integer, weDays As Integer

    <font color=448800>'The DateValue function used next makes sure we're really using a date, so we can also use strings</font color=448800>
    sDate = DateValue(startDate)
    eDate = DateValue(endDate)

    <font color=448800>'Find the nunmber of days between the two dates</font color=448800>
    nDays = eDate - sDate

    <font color=448800>'Set nWeeks = the number of weeks/weekends</font color=448800>
    If nDays >= 7 Then nWeeks = Int(nDays / 7)

    <font color=448800>'weDays = the number of weekend-days (2 * the number of weeks)</font color=448800>
    weDays = 2 * nWeeks

    <font color=448800>'If the weekday of the end-date is less than the weekday of the start date, there was a period of time</font color=448800>
    <font color=448800>'that was less than one week but which spanned a weekend, so 2 more days need to be taken off.</font color=448800>
    If Weekday(eDate) < Weekday(sDate) Then weDays = weDays + 2

    <font color=448800>'Now subtract the weekend-days from the total elapsed time</font color=448800>
    CountWeekdays = nDays - weDays

    <font color=448800>'Sunday (weekday 1) to Saturday (weekday 7) doesn't span a weekend, so weDays doesn't
    'take the last Saturday into account</font color=448800>
    If Weekday(eDate) = 7 And Weekday(sDate) = 1 Then CountWeekdays = CountWeekdays - 1

    End Function<hr>

    FWIW, It's stored in a module called EsiasDateFunctions in an add-in called esias.xla.

    It doesn't matter where I load it from; it always exhibits the same problem each time I start Excel, i.e. it appears to be loaded, but must actually be unloaded and reloaded in order to work.

    BUT at least you showed me an equivalent function, so we can proceed with the other stuff here at work, and a big thanks for that! (But I won't rest until I can figure out why I can't get the add-in to start every time Excel is launched!!!!)
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    If the code resides in a normal module, this should work.

    - Save the workbook with the code as an add-in (let excel decide the path, it should do so automatically)
    - Close the workbook, choose tools, add-ins and check the newly created addin.
    - now use the function normally.

    This worked for me in XP.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    3 Star Lounger
    Join Date
    Mar 2003
    Location
    Elkins Park, Pennsylvania, USA
    Posts
    325
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing a Custom Function (Excel XP)

    VERY peculiar! I copied the code to the clipboard and started over exactly as you described, and now it seems OK.
    My only guess is that there's something about the other file, which I did save as an add-in, but not in that location originally.
    Of course, now that I learned that a similar function already existed, it's moot, but the lesson was not wasted. (I will try to see if the new add-in can actually be distributed to other users, though.)
    Thanks for your help.
    <font face="Comic Sans MS">That's what you do in a herd; you look out for each other!</font face=comic> - Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •