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, 4 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
-
Phishers extract Millions from HMRC accounts..
by
Microfix
5 hours, 30 minutes ago -
Windows 10 22H2 Update today (5 June) says up-to-date but last was 2025-04
by
Alan_uk
7 hours, 22 minutes ago -
Thoughts on Malwarebytes Scam Guard for Mobile?
by
opti1
10 hours, 30 minutes ago -
Mystical Desktop
by
CWBillow
10 hours, 44 minutes ago -
Meta and Yandex secretly tracked billions of Android users
by
Alex5723
5 hours, 30 minutes ago -
MS-DEFCON 2: Do you need that update?
by
Susan Bradley
55 minutes ago -
CD/DVD drive is no longer recognized
by
WSCape Sand
1 day, 1 hour ago -
Windows 11 24H2 Default Apps stuck on Edge and Adobe Photoshop
by
MikeBravo
1 day, 4 hours ago -
North Face and Cartier customer data stolen in cyber attacks
by
Alex5723
1 day, 2 hours ago -
What is wrong with simple approach?
by
WSSpoke36
19 hours, 23 minutes ago -
Microsoft-Backed Builder.ai Set for Bankruptcy After Cash Seized
by
Alex5723
1 day, 14 hours ago -
Location, location, location
by
Susan Bradley
4 hours, 42 minutes ago -
Cannot get a task to run a restore point
by
CWBillow
1 day, 15 hours ago -
Frustrating search behavior with Outlook
by
MrJimPhelps
1 day, 6 hours ago -
June 2025 Office non-Security Updates
by
PKCano
2 days, 2 hours ago -
Secure Boot Update Fails after KB5058405 Installed
by
SteveIT
5 hours, 2 minutes ago -
Firefox Red Panda Fun Stuff
by
Lars220
2 days, 2 hours ago -
How start headers and page numbers on page 3?
by
Davidhs
2 days, 12 hours ago -
Attack on LexisNexis Risk Solutions exposes data on 300k +
by
Nibbled To Death By Ducks
1 day, 15 hours ago -
Windows 11 Insider Preview build 26200.5622 released to DEV
by
joep517
2 days, 21 hours ago -
Windows 11 Insider Preview build 26120.4230 (24H2) released to BETA
by
joep517
2 days, 21 hours ago -
MS Excel 2019 Now Prompts to Back Up With OneDrive
by
lmacri
2 days, 11 hours ago -
Firefox 139
by
Charlie
2 days, 3 hours ago -
Who knows what?
by
Will Fastie
1 day, 6 hours ago -
My top ten underappreciated features in Office
by
Peter Deegan
2 days, 22 hours ago -
WAU Manager — It’s your computer, you are in charge!
by
Deanna McElveen
15 hours, 48 minutes ago -
Misbehaving devices
by
Susan Bradley
1 day, 17 hours ago -
.NET 8.0 Desktop Runtime (v8.0.16) – Windows x86 Installer
by
WSmeyerbos
4 days, 4 hours ago -
Neowin poll : What do you plan to do on Windows 10 EOS
by
Alex5723
1 day, 3 hours ago -
May 31, 2025—KB5062170 (OS Builds 22621.5415 and 22631.5415 Out-of-band
by
Alex5723
4 days, 2 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.