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

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

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 -

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 -

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 -