[Excel] Macro fonctionnant sous 2003, mais plus sous 2007

[SybVicious]

Bonjour,

j'ai une macro qui fonctionnait parfaitement sous excel 2003, mais plus du tout sous 2007. Cette macro est une fonction personnalisée que j'utilise comme fonction dans des cellules :

Function NETWORKDAYSINGIVENWEEK(startingDate, endDate, analyzedWeek) As Integer
    On Error GoTo ErrorHandler

    Dim nWeek
    Dim nMont
    Dim nYear
    
    Dim firstDayOfTheWeek
    Dim lastDayOfTheWeek
    
    Dim firstDayOfTheMonth
    Dim lastDayOfTheMonth
    Dim myCase
    
    nWeek = Month(analyzedWeek)
    nMonth = Month(analyzedWeek)
    nYear = Year(analyzedWeek)
    firstDayOfTheWeek = analyzedWeek
    lastDayOfTheWeek = firstDayOfTheWeek + 5
    
    Dim bStartDateCheck, bEndDateCheck
    bStartDateCheck = IsDate(startingDate)
    bEndDateCheck = IsDate(endDate)
    
    NETWORKDAYSINGIVENWEEK = 0
    
    If ((startingDate <> "") And (endDate <> "") And bStartDateCheck And bEndDateCheck) Then
    
    
        myCase = intersectionCase(startingDate, endDate, firstDayOfTheWeek, lastDayOfTheWeek)
        
        
        Select Case myCase
        Case 0 '' No Intersaction
            NETWORKDAYSINGIVENWEEK = 0
        Case 1 '' Full Intersaction
            NETWORKDAYSINGIVENWEEK = Application.Run("NETWORKDAYS", firstDayOfTheWeek, lastDayOfTheWeek)
            
        Case 2 '' Started before and finish in the month
            NETWORKDAYSINGIVENWEEK = Application.Run("NETWORKDAYS", firstDayOfTheWeek, endDate)
                    
        Case 3 '' Started and finish in the month
            NETWORKDAYSINGIVENWEEK = Application.Run("NETWORKDAYS", startingDate, endDate)
            
        Case 4 '' Started and finish in the month
            NETWORKDAYSINGIVENWEEK = Application.Run("NETWORKDAYS", startingDate, lastDayOfTheWeek)
            
        End Select
        
    Else
        NETWORKDAYSINGIVENWEEK = 0
    End If
    
ErrorHandler:
    
    
    
End Function

________
Private Function intersectionCase(startingDate, endDate, firstDayOfThePeriod, lastDayOfThePeriod) As Integer

    ''Check if No itersection
    If Not (endDate < firstDayOfThePeriod Or startingDate > lastDayOfThePeriod) Then
       ''check if fully intersected
       If (startingDate <= firstDayOfThePeriod And endDate >= lastDayOfThePeriod) Then
            ''Case 1 : fully intersected
            intersectionCase = 1
       Else
            If (startingDate <= firstDayOfThePeriod And endDate < lastDayOfThePeriod) Then
                '' case 2 : Started before and finish in the period
                intersectionCase = 2
            End If
            
            If (startingDate > firstDayOfThePeriod And endDate < lastDayOfThePeriod) Then
                '' case 3 : Started and finish in the period
                intersectionCase = 3
            End If
            
            If (startingDate > firstDayOfThePeriod And endDate >= lastDayOfThePeriod) Then
                '' case 4 : Started  in the period and finished after
                intersectionCase = 4
            End If
       End If
       
    Else
        ''Case 0 : No itersection
        intersectionCase = 0
    End If


End Function

Auriez-vous une idée d'où provient l'erreur ?

Merci d'avance pour votre aide 🙂

 

[JNP]

Re : [Excel] Macro fonctionnant sous 2003, mais plus sous 2007

Bonjour SybVicious et bienvenue 🙂,
Il y a une erreur de variable (Dim nMonth), mais le problème ne semble pas venir de là.
Je ne connaissais pas

Application.Run("NETWORKDAYS", startingDate, lastDayOfTheWeek)

mais en remplaçant par

Application.WorksheetFunction.NetworkDays(startingDate, endDate)

il semblerait que ça fonctionne (en tout cas, ça ne me renvoie pas 0 tout le temps, mais j'ai pas suivi l'intéret de ta fonction 😱), d'où le code suivant

Function NETWORKDAYSINGIVENWEEK(startingDate, endDate, analyzedWeek) As Integer
    On Error GoTo ErrorHandler
    Dim nWeek
    Dim nMont[COLOR=red][B]h[/B][/COLOR]
    Dim nYear
    Dim firstDayOfTheWeek
    Dim lastDayOfTheWeek
    Dim firstDayOfTheMonth
    Dim lastDayOfTheMonth
    Dim myCase
    nWeek = Month(analyzedWeek)
    nMonth = Month(analyzedWeek)
    nYear = Year(analyzedWeek)
    firstDayOfTheWeek = analyzedWeek
    lastDayOfTheWeek = firstDayOfTheWeek + 5
    Dim bStartDateCheck, bEndDateCheck
    bStartDateCheck = IsDate(startingDate)
    bEndDateCheck = IsDate(endDate)
    NETWORKDAYSINGIVENWEEK = 0
    If ((startingDate <> "") And (endDate <> "") And bStartDateCheck And bEndDateCheck) Then
        myCase = intersectionCase(startingDate, endDate, firstDayOfTheWeek, lastDayOfTheWeek)
        Select Case myCase
        Case 0 '' No Intersaction
            NETWORKDAYSINGIVENWEEK = 0
        Case 1 '' Full Intersaction
            NETWORKDAYSINGIVENWEEK = [COLOR=red][B]Application.WorksheetFunction.NetworkDays(firstDayOfTheWeek, lastDayOfTheWeek)
[/B][/COLOR]        Case 2 '' Started before and finish in the month
            NETWORKDAYSINGIVENWEEK = [COLOR=red][B]Application.WorksheetFunction.NetworkDays(firstDayOfTheWeek, endDate)
[/B][/COLOR]        Case 3 '' Started and finish in the month
            NETWORKDAYSINGIVENWEEK = [COLOR=red][B]Application.WorksheetFunction.NetworkDays(startingDate, endDate)
[/B][/COLOR]        Case 4 '' Started and finish in the month
            NETWORKDAYSINGIVENWEEK = [COLOR=red][B]Application.WorksheetFunction.NetworkDays(startingDate, lastDayOfTheWeek)
[/B][/COLOR]            
        End Select
    Else
        NETWORKDAYSINGIVENWEEK = 0
    End If
ErrorHandler:
End Function

Bonne après-midi 😎

 

[SybVicious]

Re : [Excel] Macro fonctionnant sous 2003, mais plus sous 2007

JNP, mon sauveur 😀
Merci infiniment pour l'aide 🙂
Quant à l'utilisation de la formule, elle me permet de savoir en gros en combien de jour ouvrés je vais pouvoir réaliser une tâche sur une semaine donnée 🙂

Ce qui m'étonne c'est que cela marchait parfaitement sous 2003 😛

Encore merci