Alright, let's fix that French month abbreviation issue you're having with two straightforward solutions as you requested.

Your current code uses Format(premier_jour, "mmm"), which spits out French abbreviations because of your system locale. To force it to generate English abbreviations instead (so your existing month-to-number code works without changes), we can use WorksheetFunction.Text with a locale code that enforces English formatting. Here's the updated code:

premier_jour = DateSerial(Year(Date), Month(Date), 1)
dernier_jour = DateSerial(Year(Date), Month(Date) + 1, 1)
' Force English month abbreviation no matter what your system locale is
deb_period_curr = WorksheetFunction.Text(premier_jour, "[$-en-US]mmm")
With ThisWorkbook
    .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "analysis_kpi " & deb_period_curr
End With

Now your new worksheets will have names like analysis_kpi May instead of analysis_kpi mai, so your original month-to-number code won't throw errors anymore.

If you need to work with existing worksheets that already have French abbreviations, we can build a direct mapping between those French prefixes and their corresponding month numbers. Below are two options—pick whichever fits your workflow better:

Option A: Using Select Case (Simple & Direct) #

Dim month_nam As String
Dim month_num As String

month_nam = Right(Worksheet1.Name, 4)
' Trim any accidental extra spaces from the abbreviation
month_nam = Trim(month_nam)

' Match French abbreviations to their month numbers
Select Case LCase(month_nam)
    Case "jan": month_num = "01"
    Case "fév": month_num = "02"
    Case "mar": month_num = "03"
    Case "avr": month_num = "04"
    Case "mai": month_num = "05"
    Case "jun": month_num = "06"
    Case "jul": month_num = "07"
    Case "aoû": month_num = "08"
    Case "sep": month_num = "09"
    Case "oct": month_num = "10"
    Case "nov": month_num = "11"
    Case "déc": month_num = "12"
    Case Else: month_num = "Invalid" ' Catch any unexpected values
End Select

Option B: Using a Dictionary (More Scalable) #

If you think you might need to add more locale mappings later, a dictionary is cleaner and easier to update:

Dim month_nam As String
Dim month_num As String
Dim monthMap As Object

' Create a dictionary to map French abbreviations to numbers
Set monthMap = CreateObject("Scripting.Dictionary")
monthMap("jan") = "01"
monthMap("fév") = "02"
monthMap("mar") = "03"
monthMap("avr") = "04"
monthMap("mai") = "05"
monthMap("jun") = "06"
monthMap("jul") = "07"
monthMap("aoû") = "08"
monthMap("sep") = "09"
monthMap("oct") = "10"
monthMap("nov") = "11"
monthMap("déc") = "12"

month_nam = Trim(Right(Worksheet1.Name, 4))
' Check if the abbreviation exists in our map
If monthMap.Exists(LCase(month_nam)) Then
    month_num = monthMap(LCase(month_nam))
Else
    month_num = "Invalid" ' Handle cases where the abbreviation doesn't match
End If

Set monthMap = Nothing ' Clean up the dictionary object

Both options skip relying on Excel's MONTH function, which doesn't recognize French abbreviations by default, so you won't get those annoying errors anymore.

内容的提问来源于stack exchange，提问作者user9351236