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

SybVicious · 19 Avril 2010

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 :

Code:

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 · 19 Avril 2010

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

Code:

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

mais en remplaçant par

Code:

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

Code:

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 · 19 Avril 2010

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

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

SybVicious

Guest

JNP

XLDnaute Barbatruc

SybVicious

Guest

Discussions similaires

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

SybVicious

Guest

JNP

XLDnaute Barbatruc

SybVicious

Guest

Discussions similaires

Privacy & Transparency

Privacy & Transparency