Writing a Custom Function (Excel XP)

Topic

New Reply

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!

Reply | Quote

Replies

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

Reply | Quote

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??

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote