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!
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
-
Writing a Custom Function (Excel XP)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Writing a Custom Function (Excel XP)
- This topic has 23 replies, 5 voices, and was last updated 21 years, 5 months ago.
AuthorTopicWSMesaka
AskWoody LoungerJanuary 22, 2004 at 4:20 pm #399536Viewing 3 reply threadsAuthorReplies-
WSunkamunka
AskWoody LoungerJanuary 22, 2004 at 5:11 pm #772442Well, 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
-
WSMesaka
AskWoody LoungerJanuary 22, 2004 at 5:28 pm #772452Here 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?? -
WSunkamunka
AskWoody LoungerJanuary 22, 2004 at 5:39 pm #772453Check 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.
-
WSunkamunka
AskWoody LoungerJanuary 22, 2004 at 5:39 pm #772454Check 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.
-
-
-
WSunkamunka
AskWoody LoungerJanuary 22, 2004 at 5:11 pm #772443Well, 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
-
WSMesaka
AskWoody LoungerJanuary 23, 2004 at 2:48 pm #773024The 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!! -
WSMesaka
AskWoody LoungerJanuary 23, 2004 at 2:48 pm #773025The 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!! -
WSpieterse
AskWoody Lounger -
WSMesaka
AskWoody LoungerJanuary 23, 2004 at 4:34 pm #773076Sure, 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 – 1End 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!!!!)
-
WSpieterse
AskWoody LoungerJanuary 23, 2004 at 7:39 pm #773141If 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.
-
WSMesaka
AskWoody LoungerJanuary 23, 2004 at 7:57 pm #773146VERY 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. -
WSMesaka
AskWoody LoungerJanuary 23, 2004 at 7:57 pm #773147VERY 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.
-
-
WSpieterse
AskWoody LoungerJanuary 23, 2004 at 7:39 pm #773142If 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.
-
-
WSMesaka
AskWoody LoungerJanuary 23, 2004 at 4:34 pm #773077Sure, 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 – 1End 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!!!!)
-
-
WSpieterse
AskWoody Lounger -
WSKobus
AskWoody Lounger -
WSKobus
AskWoody Lounger
-
Viewing 3 reply threads -

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Intel Management Engine (Intel ME) Security Issue
by
PL1
2 hours, 25 minutes ago -
Old Geek Forced to Update. Buy a Win 11 PC? Yikes! How do I cope?
by
RonE22
35 minutes ago -
National scam day
by
Susan Bradley
2 hours, 37 minutes ago -
macOS Tahoe 26 the end of the road for Intel Macs, OCLP, Hackintosh
by
Alex5723
19 hours, 57 minutes ago -
Cyberattack on some Washington Post journalists’ email accounts
by
Bob99
21 hours, 17 minutes ago -
Tools to support internet discussions
by
Kathy Stevens
1 day, 4 hours ago -
How get Group Policy to allow specific Driver to download?
by
Tex265
12 hours, 23 minutes ago -
AI is good sometimes
by
Susan Bradley
1 day, 4 hours ago -
Mozilla quietly tests Perplexity AI as a New Firefox Search Option
by
Alex5723
18 hours, 32 minutes ago -
Perplexity Pro free for 12 mos for Samsung Galaxy phones
by
Patricia Grace
2 days, 4 hours ago -
June KB5060842 update broke DHCP server service
by
Alex5723
2 days, 3 hours ago -
AMD Ryzen™ Chipset Driver Release Notes 7.06.02.123
by
Alex5723
2 days, 7 hours ago -
Excessive security alerts
by
WSSebastian42
22 hours, 8 minutes ago -
* CrystalDiskMark may shorten SSD/USB Memory life
by
Alex5723
2 days, 16 hours ago -
Ben’s excellent adventure with Linux
by
Ben Myers
9 hours, 57 minutes ago -
Seconds are back in Windows 10!
by
Susan Bradley
2 days, 3 hours ago -
WebBrowserPassView — Take inventory of your stored passwords
by
Deanna McElveen
21 hours, 35 minutes ago -
OS news from WWDC 2025
by
Will Fastie
7 hours, 41 minutes ago -
Need help with graphics…
by
WSBatBytes
1 day, 12 hours ago -
AMD : Out of Bounds (OOB) read vulnerability in TPM 2.0 CVE-2025-2884
by
Alex5723
3 days, 8 hours ago -
Totally remove or disable BitLocker
by
CWBillow
2 days, 7 hours ago -
Windows 10 gets 6 years of ESU?
by
n0ads
2 days, 10 hours ago -
Apple, Google stores still offer China-based VPNs, report says
by
Nibbled To Death By Ducks
3 days, 19 hours ago -
Search Forums only bring up my posts?
by
Deo
7 minutes ago -
Windows Spotlight broken on Enterprise and Pro for Workstations?
by
steeviebops
4 days, 6 hours ago -
Denmark wants to dump Microsoft for Linux + LibreOffice
by
Alex5723
10 hours, 55 minutes ago -
How to get Microsoft Defender to honor Group Policy Setting
by
Ralph
3 hours, 55 minutes ago -
Apple : Paragon’s iOS Mercenary Spyware Finds Journalists Target
by
Alex5723
4 days, 17 hours ago -
Music : The Rose Room – It’s Been A Long, Long Time album
by
Alex5723
4 days, 18 hours ago -
Disengage Bitlocker
by
CWBillow
4 days, 8 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.