Using Excel functions within VBA

  • Thread starter Thread starter Duncs
  • Start date Start date
D

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
 
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)
 
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
 
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
 
Back
Top