Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Excel functions within VBA
OK, here's what I want to do.
I want to build up a text string, using dates, and then display it. Contained within that text string, will be the following information: <today's date : <number of days since start of year : <number of work days between today's data and a date in a specific cell What I'm doing is building up a string, as follows: strVar = Format(Now(), "Short Date") strVar = """" & strVar & """" & Application.International(xlListSeparator) strTempDate = Format(TempDate, "Short Date") strTempDate = """" & strTempDate & """" strVar = strVar & strTempDate strTempDate = "NETWORKDAYS(" & strVar & ")" S = S & Format(strTempDate) My theory is: strVar is set to today's date = 12/07/2007 strVar then has the appropriate separator appended to it = "12/07/2007", strTempDate is formatted as a date = 12/11/2007 strTempDate then has the '"' characters added to it = "12/11/2007" strVar is then set the two values, combined = "12/07/2007","12/11/2007" This is where it falls down. What I want to do, is insert the number of work days that are between the two values. However, with the code I have above, all I'm getting is the text "NETWORKDAYS("12/07/2007","12/11/2007")" added to my string. Can someone help? Rgds Duncs |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Excel functions within VBA
MsgBox Application.Run("ATPVBAEN.XLA!NETWORKDAYS", DateSerial(2007, 7,
12), DateSerial(2007, 11, 12)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Duncs" wrote in message oups.com... OK, here's what I want to do. I want to build up a text string, using dates, and then display it. Contained within that text string, will be the following information: <today's date : <number of days since start of year : <number of work days between today's data and a date in a specific cell What I'm doing is building up a string, as follows: strVar = Format(Now(), "Short Date") strVar = """" & strVar & """" & Application.International(xlListSeparator) strTempDate = Format(TempDate, "Short Date") strTempDate = """" & strTempDate & """" strVar = strVar & strTempDate strTempDate = "NETWORKDAYS(" & strVar & ")" S = S & Format(strTempDate) My theory is: strVar is set to today's date = 12/07/2007 strVar then has the appropriate separator appended to it = "12/07/2007", strTempDate is formatted as a date = 12/11/2007 strTempDate then has the '"' characters added to it = "12/11/2007" strVar is then set the two values, combined = "12/07/2007","12/11/2007" This is where it falls down. What I want to do, is insert the number of work days that are between the two values. However, with the code I have above, all I'm getting is the text "NETWORKDAYS("12/07/2007","12/11/2007")" added to my string. Can someone help? Rgds Duncs |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Excel functions within VBA
Eureka!
No, I've not got it working, but I've found out why it may not be working! The Data Analysis Toolpak isn't installed and, due to security lock-down on Excel in my organisation, the Tools menu is disabled. So, I can't activate it. Where now? Does anyone have any VBA code, that will do the same thing? TIA Duncs On 12 Jul, 12:26, "Bob Phillips" wrote: MsgBox Application.Run("ATPVBAEN.XLA!NETWORKDAYS", DateSerial(2007, 7, 12), DateSerial(2007, 11, 12)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Duncs" wrote in message oups.com... OK, here's what I want to do. I want to build up a text string, using dates, and then display it. Contained within that text string, will be the following information: <today's date : <number of days since start of year : <number of work days between today's data and a date in a specific cell What I'm doing is building up a string, as follows: strVar = Format(Now(), "Short Date") strVar = """" & strVar & """" & Application.International(xlListSeparator) strTempDate = Format(TempDate, "Short Date") strTempDate = """" & strTempDate & """" strVar = strVar & strTempDate strTempDate = "NETWORKDAYS(" & strVar & ")" S = S & Format(strTempDate) My theory is: strVar is set to today's date = 12/07/2007 strVar then has the appropriate separator appended to it = "12/07/2007", strTempDate is formatted as a date = 12/11/2007 strTempDate then has the '"' characters added to it = "12/11/2007" strVar is then set the two values, combined = "12/07/2007","12/11/2007" This is where it falls down. What I want to do, is insert the number of work days that are between the two values. However, with the code I have above, all I'm getting is the text "NETWORKDAYS("12/07/2007","12/11/2007")" added to my string. Can someone help? Rgds Duncs- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Excel functions within VBA
Try this:
'-------Start of Code------- Function CalcNetWorkdays(StartDate As Date, EndDate As Date) Dim iCtr As Integer Dim iWorkdayCount As Integer With WorksheetFunction For iCtr = 2 To 6 iWorkdayCount = iWorkdayCount + Int((.Weekday(StartDate - iCtr) + EndDate - StartDate) / 7) Next iCtr End With CalcNetWorkdays = iWorkdayCount End Function '-------End of Code------- Note: That function doesn't accomodate holidays. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Duncs" wrote: Eureka! No, I've not got it working, but I've found out why it may not be working! The Data Analysis Toolpak isn't installed and, due to security lock-down on Excel in my organisation, the Tools menu is disabled. So, I can't activate it. Where now? Does anyone have any VBA code, that will do the same thing? TIA Duncs On 12 Jul, 12:26, "Bob Phillips" wrote: MsgBox Application.Run("ATPVBAEN.XLA!NETWORKDAYS", DateSerial(2007, 7, 12), DateSerial(2007, 11, 12)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Duncs" wrote in message oups.com... OK, here's what I want to do. I want to build up a text string, using dates, and then display it. Contained within that text string, will be the following information: <today's date : <number of days since start of year : <number of work days between today's data and a date in a specific cell What I'm doing is building up a string, as follows: strVar = Format(Now(), "Short Date") strVar = """" & strVar & """" & Application.International(xlListSeparator) strTempDate = Format(TempDate, "Short Date") strTempDate = """" & strTempDate & """" strVar = strVar & strTempDate strTempDate = "NETWORKDAYS(" & strVar & ")" S = S & Format(strTempDate) My theory is: strVar is set to today's date = 12/07/2007 strVar then has the appropriate separator appended to it = "12/07/2007", strTempDate is formatted as a date = 12/11/2007 strTempDate then has the '"' characters added to it = "12/11/2007" strVar is then set the two values, combined = "12/07/2007","12/11/2007" This is where it falls down. What I want to do, is insert the number of work days that are between the two values. However, with the code I have above, all I'm getting is the text "NETWORKDAYS("12/07/2007","12/11/2007")" added to my string. Can someone help? Rgds Duncs- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Excel functions within VBA
Thanks Ron. I can definitely work with this.
Duncs On 12 Jul, 13:34, Ron Coderre wrote: Try this: '-------Start of Code------- Function CalcNetWorkdays(StartDate As Date, EndDate As Date) Dim iCtr As Integer Dim iWorkdayCount As Integer With WorksheetFunction For iCtr = 2 To 6 iWorkdayCount = iWorkdayCount + Int((.Weekday(StartDate - iCtr) + EndDate - StartDate) / 7) Next iCtr End With CalcNetWorkdays = iWorkdayCount End Function '-------End of Code------- Note: That function doesn't accomodate holidays. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Duncs" wrote: Eureka! No, I've not got it working, but I've found out why it may not be working! The Data Analysis Toolpak isn't installed and, due to security lock-down on Excel in my organisation, the Tools menu is disabled. So, I can't activate it. Where now? Does anyone have any VBA code, that will do the same thing? TIA Duncs On 12 Jul, 12:26, "Bob Phillips" wrote: MsgBox Application.Run("ATPVBAEN.XLA!NETWORKDAYS", DateSerial(2007, 7, 12), DateSerial(2007, 11, 12)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Duncs" wrote in message roups.com... OK, here's what I want to do. I want to build up a text string, using dates, and then display it. Contained within that text string, will be the following information: <today's date : <number of days since start of year : <number of work days between today's data and a date in a specific cell What I'm doing is building up a string, as follows: strVar = Format(Now(), "Short Date") strVar = """" & strVar & """" & Application.International(xlListSeparator) strTempDate = Format(TempDate, "Short Date") strTempDate = """" & strTempDate & """" strVar = strVar & strTempDate strTempDate = "NETWORKDAYS(" & strVar & ")" S = S & Format(strTempDate) My theory is: strVar is set to today's date = 12/07/2007 strVar then has the appropriate separator appended to it = "12/07/2007", strTempDate is formatted as a date = 12/11/2007 strTempDate then has the '"' characters added to it = "12/11/2007" strVar is then set the two values, combined = "12/07/2007","12/11/2007" This is where it falls down. What I want to do, is insert the number of work days that are between the two values. However, with the code I have above, all I'm getting is the text "NETWORKDAYS("12/07/2007","12/11/2007")" added to my string. Can someone help? Rgds Duncs- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel functions | Excel Worksheet Functions | |||
efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
User-defined functions created in Excel 2000 fail in Excel 2003 | Excel Discussion (Misc queries) | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions |