• Writing a Custom Function (Excel XP)

    Author
    Topic
    #399536

    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!

    Viewing 3 reply threads
    Author
    Replies
    • #772442

      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 Star Post. HTH

      • #772452

        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 C:Documents 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??

        • #772453

          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. smile

          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.

          • #772524

            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.

            • #772574

              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

            • #772575

              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

          • #772525

            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.

        • #772454

          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. smile

          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.

    • #772443

      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 Star Post. HTH

    • #773024

      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,B3).”
      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!!

    • #773025

      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,B3).”
      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!!

      • #773042

        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.

        • #773076

          Sure, here is the code:

          [indent]


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

          ‘The DateValue function used next makes sure we’re really using a date, so we can also use strings
          sDate = DateValue(startDate)
          eDate = DateValue(endDate)

          ‘Find the nunmber of days between the two dates
          nDays = eDate – sDate

          ‘Set nWeeks = the number of weeks/weekends
          If nDays >= 7 Then nWeeks = Int(nDays / 7)

          ‘weDays = the number of weekend-days (2 * the number of weeks)
          weDays = 2 * nWeeks

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

          ‘Now subtract the weekend-days from the total elapsed time
          CountWeekdays = nDays – weDays

          ‘Sunday (weekday 1) to Saturday (weekday 7) doesn’t span a weekend, so weDays doesn’t
          ‘take the last Saturday into account

          If Weekday(eDate) = 7 And Weekday(sDate) = 1 Then CountWeekdays = CountWeekdays – 1

          End Function


          [/indent]

          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!!!!)

          • #773141

            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.

            • #773146

              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.

            • #773147

              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.

          • #773142

            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.

        • #773077

          Sure, here is the code:

          [indent]


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

          ‘The DateValue function used next makes sure we’re really using a date, so we can also use strings
          sDate = DateValue(startDate)
          eDate = DateValue(endDate)

          ‘Find the nunmber of days between the two dates
          nDays = eDate – sDate

          ‘Set nWeeks = the number of weeks/weekends
          If nDays >= 7 Then nWeeks = Int(nDays / 7)

          ‘weDays = the number of weekend-days (2 * the number of weeks)
          weDays = 2 * nWeeks

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

          ‘Now subtract the weekend-days from the total elapsed time
          CountWeekdays = nDays – weDays

          ‘Sunday (weekday 1) to Saturday (weekday 7) doesn’t span a weekend, so weDays doesn’t
          ‘take the last Saturday into account

          If Weekday(eDate) = 7 And Weekday(sDate) = 1 Then CountWeekdays = CountWeekdays – 1

          End Function


          [/indent]

          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!!!!)

      • #773043

        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.

      • #773056

        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?

        • #773074

          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

        • #773075

          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

      • #773057

        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?

    Viewing 3 reply threads
    Reply To: Writing a Custom Function (Excel XP)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: