• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2010 user; need help revising code using the Excel 2013-only DAYS function please

L

lingyai

New Member
Hi all,

I recently got a book of example models using VBA and while it's gotten good reviews and seems to have some interesting stuff, the first example I played with (attached) doesn't run properly. This is for a simulation of project delays. When I run the following code

Code: 
Sub ProjectDelays()
    Dim oTable As Range, i As Integer, sMsg As String, dAvgDate As Date, iOff As Long
    With Range("E2:H11")
        .ClearContents
        .Columns(1).Formula = "=C2+D2-1"
        .Columns(2).Formula = "=D2+RANDBETWEEN(-2,2)"
        .Columns(3).Formula = "=IF(ROW()=2,C2, H1+1)"
        .Columns(4).Formula = "=G2+F2-1"
    End With
    Set oTable = Range("G14").CurrentRegion
    Range("H15:H115").ClearContents
    oTable.Table , Range("F13")
    Range("E14:E33") = WorksheetFunction.Frequency(oTable.Columns(2), Range("D14:D33"))
    'OR: Range("E14:E33").FormulaArray = "=FREQUENCY(R14C8:R115C8,R14C4:R33C4)"
    dAvgDate = Round(WorksheetFunction.Average(oTable.Columns(2)), 0)
    iOff = WorksheetFunction.Days(dAvgDate, Range("E11")) '+ IIf(iOff >= 0, 1, -1)
    If dAvgDate = Range("E11") Then iOff = 0
    sMsg = "Average finish date of 100 runs: " & FormatDateTime(dAvgDate, vbShortDate) & vbCr
    sMsg = sMsg & "On average " & Abs(iOff) & " days " & IIf(iOff >= 0, "later", "earlier")
    sMsg = sMsg & " than " & Range("E11")
    MsgBox sMsg
End Sub

I get an "Object does not support this property or method" error message, and in the

VBA Editor this is highlighted:

Code: 
 iOff = WorksheetFunction.Days(dAvgDate, Range("E11")) '+ IIf(iOff >= 0, 1, -1)

Ok, I'm on Excel 2010 and so I lack the DAYS worksheet function. I read elsewhere that I should use the DATEDIF fucntion instead. So I changed the code to

Code: 
iOff = WorksheetFunction.DatedIf(dAvgDate, Range("E11"), "d") '+ IIf(iOff >= 0, 1, -1)
and it still doesn't work.

Sorry, I'm new to VBA (which is why I got the book, to learn by example), and the author seems to be uncontactable. Could anyone advise?
 

Attachments

You must log in or register to reply here.
Back
Top