asked on

VBA Excel 2000 - Week number

Dear Experts,

I have a short code part which works fine for days and months from todays date

Dim SaveFileDay As String
Dim SaveFileMonth As String
SaveFileDay = Day(Date)
SaveFileMonth = Month(Date)

I would like to do the same but for weeks so determining the current number of week from todays date, but I am not sure in the syntax as the below logic does not work

Dim SaveFileWeek As String
SaveFileWeek = Weeknum(Date)

Could you please advise how it should be?

thanks,

dlmille

Try:

SaveFileWeek = application.WorksheetFunction.WeekNum(Date)

Dave

dlmille

PS - if you use the HELP (F1) in the VBA Project area, you can look up: WorksheetFunction Object Members and then drill down on that to see all the worksheet functions that are available to you.

Cheers,

Dave

csehz

ASKER

Dave thanks, applying this row I got error message that

Object does not support this property or method

I assume it is because like on the attached picture, maybe in Excell 2000 the Weeknum is not available? In that case do you have maybe some alternative solution?
wsf.jpg

dlmille

Try APPLICATION.Worksheetfunction ?

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Dave thanks, it is interesting that anyway as normal function in a cell that works for me, so finally applied this

SaveFileWeek = Evaluate("=WEEKNUM(TODAY())")

thanks just again,

dlmille

Good one. I've recently (today) had problems with Evaluate in a macro (run many,many times appears to create excel/display/memory errors).

One potential alternative to evaluate is the following:

Function myEvaluate(testStr As String, fromMacro As Boolean) As Boolean
'attempt to do a better job than Application.Evaluate to evaluate these operations, as the Excel 4.0 macro calls (lots of them) create memory errors

    myEvaluate = False
      
    'First, check to see if this is a worksheet function, or is running from a macro
    
    'fall thru with FALSE evaluation on comparisons that don't make sense - e.g., comparing a string with a numeric equality
    On Error Resume Next
    If fromMacro Then 'must be running from a macro so leveraging defined name OK
        ActiveWorkbook.Names.Add Name:="toEvaluate", RefersToR1C1:="=False"
    
        ActiveWorkbook.Names("toEvaluate").RefersTo = "=" & testStr
        
        myEvaluate = [toEvaluate]
        
        ActiveWorkbook.Names("toEvaluate").Delete
    Else 'must be running as a worksheet function, so should be "safer" to run as-is with evaluate, as can't make defined name at this level
        myEvaluate = Application.Evaluate(testStr)
    End If
    
    If Err.Number <> 0 Then
        myEvaluate = False
    End If
    Err.Clear
    On Error GoTo 0
    
End Function

Open in new window

Dave

csehz

ASKER

Thanks the info, good to know..