Using Excel functions within VBA

[Duncs]

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

 

[Bob Phillips]

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]

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

 

[Ron Coderre]

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]

Thanks Ron. I can definitely work with this.

Duncs