9 : l'indice n'appartient pas à la sélection

Question

Bonjour, 

j'ai un problème de code.
 voici le message d'erreur:

9: l'indice n'appartient pas à la sélection.
Source=CmdTraitementCalculs_ClickBTInputs:GenerateInputsFiles

le Code:

Private Sub CmdTraitementCalculs_Click()

On Error GoTo errHandler

Dim DestinationFolder As String
    
    Dim TabInputs As Variant

FileBTData = "BT " & txtAnnee.TEXT & " - DATA.xls"

'Vérifier si l'utilisateur a bien selectionné un repertoire destination
    DestinationFolder = Txt_repDest.TEXT & "\"
    If DestinationFolder = "\" Then
        MsgBox "Vous devez selection un répertoire de destination.", vbOKOnly + vbExclamation, "BT Inputs B2S"
        Exit Sub
    End If
    
    'Vérifier l'existence du fichier BT_DATA.xls
    If Not FileExists(DestinationFolder & FileBTData) Then
        MsgBox "Le fichier de données " & FileBTData & " n'exsite pas. Il faut le générer.", vbOKOnly + vbExclamation, "BT Inputs B2S"
        Exit Sub
    End If
    
    '   Affiche la fenetre Message
    Me.MousePointer = fmMousePointerHourGlass
    Me.Hide
    
    frmMsgTrait.cmdOk.Visible = False
    MsgTraitLabel1 "Traitement génération des fichiers Inputs ...."
    frmMsgTrait.Show vbModeless

TabInputs = GetInputsList(DestinationFolder)
    
    Call GenerateInputsFiles(DestinationFolder, TabInputs)
    
    Erase TabInputs
    
    MsgTraitLabel1 "Traitement génération des fichiers Inputs terminé!"
    MsgTraitLabel2 ""
    frmMsgTrait.cmdOk.Visible = True
    
    Me.MousePointer = fmMousePointerArrow
Exit Sub
errHandler:
    MsgBox err.Number & " : " & err.Description & vbCrLf & "Source=" & "CmdTraitementCalculs_Click:" & err.Source
    Application.ScreenUpdating = True
    Me.MousePointer = fmMousePointerArrow
    Unload frmMsgTrait
    Unload Me
End Sub


Merci d'avance pour votre aide.

EDIT : Ajout des balises de code (la coloration syntaxique).
Explications disponibles ici :
https://codes-sources.commentcamarche.net/faq/10686-le-nouveau-codes-sources-comment-ca-marche#balises-code 

Merci d'y penser dans tes prochains messages.
.

Configuration: Windows 7 / Internet Explorer 8.0

jordane45 · Answer

Bonjour,
Il semble que ton message indique que le souci se situe sur ta fonction

Call GenerateInputsFiles(DestinationFolder, TabInputs)


Que contient cette fonction ?
As tu vérifié qu'elle fonctionne ?
Que contiennent tes variables  DestinationFolder et TabInputs au moment de l'erreur ?

Denirouu · Answer

Bonjour Jordane45, Merci pour ta réponse. Je teins à te préciser que je suis un novice sur VBA et sur ce Forum. J'ai hérité de cette macro, je suis en train de faire quelques modifications et de rajouter des fonctions afin qu'elle soit plus adaptée à mon besoin. Ci-dessous le contenu de la fonction : Public Sub GenerateInputsFiles(DestinationFolder As String, TabInputs As Variant) On Error GoTo errHandler Dim DestFolder As String Dim InputFileName As String Dim WkbInput As Workbook Dim TabDealData As Variant Dim ParamBT As Variant Dim i As Integer Dim j As Integer Dim InputInfo As InputObj Dim InputEnCours As InputObj Dim RowInitialID As Integer Dim SheetName As String Dim InputNb As Integer Dim DealNb As Integer Dim TabInputsAnalyse As Variant Dim TabInputsAnalyse2 As Variant ReDim TabInputsAnalyse(4, -1 To -1) ReDim TabInputsAnalyse2(4, -1 To -1) Dim SH As Worksheet 'Destination contient le répertoire dans lequel il faut générer les fichiers. 'TabInputs contient les données ConduitName/InputType/InputCode/InputName/Paramètres frmMsgTrait.MousePointer = fmMousePointerHourGlass Application.ScreenUpdating = False 'Compteur d'input InputNb = 0 'Compteur de deal par input DealNb = 0 'Pour chaque deal: For j = 0 To UBound(TabInputs, 2) InputInfo.Conduit = TabInputs(0, j) InputInfo.Type = TabInputs(1, j) InputInfo.Name = TabInputs(4, j) InputInfo.Code = IIf(IsNull(TabInputs(2, j)), "", TabInputs(2, j)) InputInfo.Deal = TabInputs(3, j) InputInfo.Param = TabInputs(5, j) & "|" & TabInputs(6, j) 'Recuperer les données du deal TabDealData = GetDealData(DestinationFolder, InputInfo) If IsNull(TabDealData) Then Else If InputEnCours.Conduit & InputEnCours.Type & InputEnCours.Name <> InputInfo.Conduit & InputInfo.Type & InputInfo.Name Then If InputEnCours.Conduit <> "" Then Application.DisplayAlerts = False Sheets("Default Rate (Vasicek) Results").Delete Sheets("Other Inputs Results").Delete Sheets("Deal (Default Rate (Vasicek))").Delete Sheets("Deal (Dilution Rate)").Delete Sheets("Deal (Default Rate STD)").Delete Sheets("Deal (DSO and LHR)").Delete Sheets("Deal (Other Inputs)").Delete Application.DisplayAlerts = True 'Sur la feuille Bilan inserer les lignes pour le deal et mettre les formules dans les bonnes cases Worksheets("Results").Select Call UpdateInputsBilanSheet(InputEnCours.Name) Calculate WkbInput.Save Call UpdateSyntheseScores(DestinationFolder, InputEnCours, TabInputsAnalyse, TabInputsAnalyse2) WkbInput.Close Set WkbInput = Nothing ' Debug.Print Application.MemoryUsed & " + " & Application.MemoryFree & " = " & Application.MemoryTotal ' Call ClearClipboard ' Debug.Print Application.MemoryUsed & " + " & Application.MemoryFree & " = " & Application.MemoryTotal DealNb = 0 End If InputEnCours = InputInfo 'ActualInput = InputInfo.Conduit & InputInfo.Type & InputInfo.Name 'Vérifier l'existance du rep destination 's'il n'existe pas le créér = Destination\ConduitName\ConduitName - InputType DestFolder = DestinationFolder & InputInfo.Conduit If Not PathExists(DestFolder) Then MkDir DestFolder End If DestFolder = DestFolder & "\" & InputInfo.Conduit & " - " & InputInfo.Type If Not PathExists(DestFolder) Then MkDir DestFolder End If DestFolder = DestFolder & "\" 'Ouvrir le classeur template BT -Template inputs.xls Workbooks.Open (ThisWorkbook.Path & "\Templates\BT - Template inputs.xls") 'replacement des caractères interdits dans le nom du fichier InputFileName = Mid(FileBTData, 1, 7) & " - Inputs - " & InputInfo.Conduit & " " & InputInfo.Type & " - " & InputInfo.Name InputFileName = Replace(InputFileName, "/", " ") InputFileName = Replace(InputFileName, "\", " ") InputFileName = Replace(InputFileName, ":", " ") InputFileName = Replace(InputFileName, "*", " ") InputFileName = Replace(InputFileName, "?", " ") InputFileName = Replace(InputFileName, """", " ") InputFileName = Replace(InputFileName, ">", " ") InputFileName = Replace(InputFileName, "<", " ") InputFileName = Replace(InputFileName, "|", " ") InputFileName = DestFolder & InputFileName 'Enregister Sous Destination\ConduitName\ConduitName - InputType\BT - Inputs - ConduitName InputType - InputName.xls Application.DisplayAlerts = False ActiveWorkbook.SaveAs FileName:=InputFileName Application.DisplayAlerts = True Select Case UCase(InputInfo.Name) Case "DEFAULT RATE (VASICEK)" Sheets("Default Rate (Vasicek) Results").Copy Before:=Sheets(1) ActiveSheet.Name = "Results" Case "LHR", "DSO" Sheets("Other Inputs Results").Copy Before:=Sheets(1) ActiveSheet.Name = "Results" Case Else Sheets("Other Inputs Results").Copy Before:=Sheets(1) ActiveSheet.Name = "Results" End Select Call ClearClipboard InputNb = InputNb + 1 MsgTraitLabel2 "En cours : " & Mid(FileBTData, 1, 7) & " - Inputs - " & InputInfo.Conduit & " " & InputInfo.Type & " - " & InputInfo.Name 'Desactiver le mode calcul With Application .Calculation = xlManual .CalculateBeforeSave = False End With Set WkbInput = ActiveWorkbook End If 'Copier la feuille Deal Select Case UCase(InputInfo.Name) Case "DEFAULT RATE (VASICEK)" Sheets("Deal (Default Rate (Vasicek))").Copy After:=Sheets(8) Case "DEFAULT RATE STD DEVIATION" Sheets("Deal (Default Rate STD)").Copy After:=Sheets(8) Case "DILUTION RATE" Sheets("Deal (Dilution Rate)").Copy After:=Sheets(8) Case "LHR", "DSO" Sheets("Deal (DSO and LHR)").Copy After:=Sheets(8) Case Else Sheets("Deal (Other Inputs)").Copy After:=Sheets(8) End Select Call ClearClipboard ' Debug.Print Application.MemoryUsed & " + " & Application.MemoryFree & " = " & Application.MemoryTotal ' Application.CutCopyMode = False ' Debug.Print Application.MemoryUsed & " + " & Application.MemoryFree & " = " & Application.MemoryTotal RowInitialID = 0 'Renommer la feuille avec DealName SheetName = "EnCours" ActiveSheet.Name = SheetName Worksheets(SheetName).Cells(16, 6) = TabDealData(6, 0) Worksheets(SheetName).Cells(25, 1).Select 'Insert les données au bon endroit For i = 0 To UBound(TabDealData, 2) RowInitialID = ActiveCell.Row If CStr(Worksheets(SheetName).Cells(RowInitialID, 1).Value) = CStr(DateAdd("d", -1, CDate("01" & "/" & Month(TabDealData(6, i)) + 1 & "/" & Year(TabDealData(6, i))))) Or CStr(Worksheets(SheetName).Cells(RowInitialID, 1).Value) = "" Then Worksheets(SheetName).Cells(RowInitialID, 1).Value = DateAdd("d", -1, DateAdd("m", 1, CDate("01" & "/" & Month(TabDealData(6, i)) & "/" & Year(TabDealData(6, i))))) Worksheets(SheetName).Cells(RowInitialID, 2).Value = TabDealData(7, i) Worksheets(SheetName).Cells(RowInitialID, 5).Value = TabDealData(8, i) Worksheets(SheetName).Cells(RowInitialID, 1).Select Else While CDate("01" & "/" & Month(ActiveCell) & "/" & Year(ActiveCell)) <> CDate("01" & "/" & Month(TabDealData(6, i)) & "/" & Year(TabDealData(6, i))) RowInitialID = RowInitialID + 1 If RowInitialID > 150 Then Rows(RowInitialID & ":" & RowInitialID).Insert Shift:=xlDown RowInitialID = RowInitialID + 1 End If Worksheets(SheetName).Cells(RowInitialID, 1).Select Worksheets(SheetName).Cells(RowInitialID, 1).Value = DateAdd("d", -1, DateAdd("m", 2, CDate("01" & "/" & Month(Cells(RowInitialID - 1, 1).Value) & "/" & Year(Cells(RowInitialID - 1, 1).Value)))) Wend Worksheets(SheetName).Cells(RowInitialID, 2) = TabDealData(7, i) Worksheets(SheetName).Cells(RowInitialID, 5) = TabDealData(8, i) End If Next i RowInitialID = ActiveCell.Row If RowInitialID < 150 Then Rows(RowInitialID + 1 & ":150").Delete 'Worksheets(SheetName).Range("F25:BN25").AutoFill Destination:=Worksheets(SheetName).Range("F25:BN" & RowInitialID), Type:=xlFillDefault 'Worksheets(SheetName).Range("C25:D25").AutoFill Destination:=Worksheets(SheetName).Range("C25:D" & RowInitialID), Type:=xlFillDefault Worksheets(SheetName).Cells(17, 6) = TabDealData(6, i - 1) If InputInfo.Param <> "" Then ParamBT = Split(InputInfo.Param, "|") 'Cells(15, 3) = ParamBT(0) Worksheets(SheetName).Cells(16, 3) = CDbl(ParamBT(0)) Worksheets(SheetName).Cells(17, 3) = CDbl(ParamBT(1)) End If 'Formatage Worksheets(SheetName).Range("A" & RowInitialID & ":A" & RowInitialID).Borders(xlEdgeBottom).LineStyle = xlContinuous Worksheets(SheetName).Range("AC25:AC" & RowInitialID).Borders(xlInsideVertical).LineStyle = xlNone Worksheets(SheetName).Range("AC25:AC" & RowInitialID).Borders(xlInsideHorizontal).LineStyle = xlNone Worksheets(SheetName).Range("AC" & RowInitialID & ":AC" & RowInitialID).Borders(xlEdgeBottom).LineStyle = xlContinuous If UCase(InputInfo.Name) = "DEFAULT RATE (VASICEK)" Then Worksheets(SheetName).Range("AL25:AL" & RowInitialID).Borders(xlInsideVertical).LineStyle = xlNone Worksheets(SheetName).Range("AL25:AL" & RowInitialID).Borders(xlInsideHorizontal).LineStyle = xlNone Worksheets(SheetName).Range("AL" & RowInitialID & ":AL" & RowInitialID).Borders(xlEdgeBottom).LineStyle = xlContinuous End If Worksheets(SheetName).Range("A1:D1").Select WkbInput.Save Worksheets(SheetName).ChartObjects(1).Activate ActiveChart.ChartTitle.TEXT = InputInfo.Name & " - " & InputInfo.Deal 'Ajout de la donnée « 3 months average » si elle existe Worksheets(SheetName).Cells(25, 1).Select For i = 0 To UBound(TabDealData, 2) RowInitialID = ActiveCell.Row If CStr(Worksheets(SheetName).Cells(RowInitialID, 1).Value) = CStr(DateAdd("d", -1, CDate("01" & "/" & Month(TabDealData(6, i)) + 1 & "/" & Year(TabDealData(6, i))))) Then If TabDealData(9, i) <> "" Then Worksheets(SheetName).Cells(RowInitialID, 3).Value = TabDealData(9, i) Worksheets(SheetName).Cells(RowInitialID, 1).Select Else While CDate("01" & "/" & Month(ActiveCell) & "/" & Year(ActiveCell)) <> CDate("01" & "/" & Month(TabDealData(6, i)) & "/" & Year(TabDealData(6, i))) RowInitialID = RowInitialID + 1 Worksheets(SheetName).Cells(RowInitialID, 1).Select Wend If TabDealData(9, i) <> "" Then Worksheets(SheetName).Cells(RowInitialID, 3) = TabDealData(9, i) Worksheets(SheetName).Cells(RowInitialID, 1).Select End If Next i ActiveSheet.Cells(1, 1).Select Worksheets(SheetName).Name = InputInfo.Code & InputInfo.Deal WkbInput.Save ' Debug.Print Application.MemoryUsed & " + " & Application.MemoryFree & " = " & Application.MemoryTotal ' Application.CutCopyMode = False ' Debug.Print Application.MemoryUsed & " + " & Application.MemoryFree & " = " & Application.MemoryTotal DealNb = DealNb + 1 DoEvents End If Next j Application.DisplayAlerts = False Sheets("Default Rate (Vasicek) Results").Delete Sheets("Other Inputs Results").Delete Sheets("Deal (Default Rate (Vasicek))").Delete Sheets("Deal (Dilution Rate)").Delete Sheets("Deal (Default Rate STD)").Delete Sheets("Deal (DSO and LHR)").Delete Sheets("Deal (Other Inputs)").Delete Application.DisplayAlerts = True 'Sur la feuille Bilan inserer les lignes pour le deal et mettre les formules dans les bonnes cases Worksheets("Results").Select Call UpdateInputsBilanSheet(CStr(TabInputs(4, j - 1))) Calculate WkbInput.Save Call UpdateSyntheseScores(DestinationFolder, InputEnCours, TabInputsAnalyse, TabInputsAnalyse2) WkbInput.Close Call ClearClipboard Set WkbInput = Nothing 'Synthese des inputs Analyses Call UpdateSyntheseAnalyses(DestinationFolder, TabInputsAnalyse, TabInputsAnalyse2) Workbooks(Mid(FileBTData, 1, 7) & " - Inputs Results.xls").Activate For Each SH In Worksheets SH.Activate Cells(1, 1).Select Next Sheets(1).Select ActiveWorkbook.Save Workbooks(Mid(FileBTData, 1, 7) & " - Inputs Results.xls").Close Application.ScreenUpdating = True frmMsgTrait.MousePointer = fmMousePointerArrow Erase TabDealData Erase ParamBT Exit Sub errHandler: Set WkbInput = Nothing Err.Raise Err.Number, "BTInputs:GenerateInputsFiles", Err.Description Application.ScreenUpdating = True End Sub Merci. Denirouu EDIT : Ajout du LANGAGE dans les balises de code (la coloration syntaxique). Explications disponibles ici : https://codes-sources.commentcamarche.net/faq/10686-le-nouveau-codes-sources-comment-ca-marche#balises-code Merci d'y penser dans tes prochains messages. .

jordane45 · Answer

Il faudrait que tu mettes un point d'arrêt dans ton code ... et que tu le fasses dérouler en mode pas à pas pour essayer de savoir sur quelle ligne il bloque exactement...

Denirouu · Answer

c'est ce que j'ai fait.
Je n ai pas d'indication sur la ligne, il y a juste le message d'erreur qui s'affiche avec aucune option de débogage qui me permettrait d'identifier la ligne.

cela peut il etre du au message d'erreur ci-dessous :

9 : l'indice n'appartient pas à la sélection

4 réponses

Discussions similaires

Newsletters