Bonjour,
Je rencontre actuellement un problème lors de l'appel d'une fonction dans une autre.
En effet, excel me renvoie "argument non facultatif".
Pourtant je pense renseigner tous les arguments demandés.
Voici le code de la fonction qui appelle l'autre :
Sub info(descr As String)
Dim e As Integer
Dim d As String
Dim k As Variant
Application.ScreenUpdating = False
d = descr
Call CreateInfoSheet("info", d) 'c'est ici que la fonction est appelée!
e = EmptyLine("info", 1)
If e = 3 Then
Worksheets("Main" ).Select
MsgBox "Component Deleted"
Else
For i = 3 To e - 1
Worksheets("Main" ).Select
Select Case MsgBox("There is " & e - 3 & " Locations" & Chr(10) & _
" " & Chr(10) & _
"Location " & i - 2 & " : " & Chr(10) & _
" " & Chr(10) & _
"Room : " & Sheets("info" ).Range("C" & i) & Chr(10) & _
"Cupboard : " & Sheets("info" ).Range("D" & i) & Chr(10) & _
"Box : " & Sheets("info" ).Range("E" & i) & Chr(10) & _
"Location : " & Sheets("info" ).Range("F" & i) & Chr(10) & _
"Quantity : " & Sheets("info" ).Range("G" & i) & Chr(10) & _
" " & Chr(10) & _
"More Info?", vbYesNo, "location founded" )
Case vbYes
MsgBox "Location " & i - 2 & " : " & Chr(10) & _
" " & Chr(10) & _
"Room : " & Sheets("info" ).Range("C" & i) & Chr(10) & _
"Cupboard : " & Sheets("info" ).Range("D" & i) & Chr(10) & _
"Box : " & Sheets("info" ).Range("E" & i) & Chr(10) & _
"Location : " & Sheets("info" ).Range("F" & i) & Chr(10) & _
"Quantity : " & Sheets("info" ).Range("G" & i) & Chr(10) & _
"Price/pieces : " & Sheets("info" ).Range("H" & i) & Chr(10) & _
"Total Price : " & Sheets("info" ).Range("I" & i) & Chr(10) & _
"Company : " & Sheets("info" ).Range("J" & i) & Chr(10) & _
"Link : " & Sheets("info" ).Range("K" & i)
Case vbNo
Worksheets("Main" ).Select
End Select
Next
End If
End Sub
Et voici le code de la fonction appelée :
Sub CreateInfoSheet(name As String, descr As String)
Dim a As Variant
Dim b As String
Dim k As Integer
Dim l As Integer
Dim d As String
l = 3
k = EmptyLine("All components", 2)
b = name
d = descr
Application.ScreenUpdating = False
'new sheet's creation
Application.DisplayAlerts = False
If SheetExist(b) = True Then
Sheets(b).Delete
End If
Sheets("All components" ).Select
Range("A1" ).Select
a = Selection.EntireRow
Sheets("Main" ).Select
Sheets.Add
ActiveSheet.name = b
Columns("A:A" ).ColumnWidth = Sheets("All components" ).Columns("A:A" ).ColumnWidth
Columns("B:B" ).ColumnWidth = Sheets("All components" ).Columns("B:B" ).ColumnWidth
Columns("C:C" ).ColumnWidth = Sheets("All components" ).Columns("C:C" ).ColumnWidth
Columns("D:D" ).ColumnWidth = Sheets("All components" ).Columns("D:D" ).ColumnWidth
Columns("E:E" ).ColumnWidth = Sheets("All components" ).Columns("E:E" ).ColumnWidth
Columns("F:F" ).ColumnWidth = Sheets("All components" ).Columns("F:F" ).ColumnWidth
Columns("G:G" ).ColumnWidth = Sheets("All components" ).Columns("G:G" ).ColumnWidth
Columns("H:H" ).ColumnWidth = Sheets("All components" ).Columns("H:H" ).ColumnWidth
Columns("I:I" ).ColumnWidth = Sheets("All components" ).Columns("I:I" ).ColumnWidth
Columns("J:J" ).ColumnWidth = Sheets("All components" ).Columns("J:J" ).ColumnWidth
Columns("K:K" ).ColumnWidth = Sheets("All components" ).Columns("K:K" ).ColumnWidth
Columns("L:L" ).ColumnWidth = Sheets("All components" ).Columns("L:L" ).ColumnWidth
Columns("M:M" ).ColumnWidth = Sheets("All components" ).Columns("M:M" ).ColumnWidth
Columns("N:N" ).ColumnWidth = Sheets("All components" ).Columns("N:N" ).ColumnWidth
'Sheet's Title
Range("A1:C1" ).Select
Selection.Merge
Sheets(b).Cells(1, 1).Value = b & ", " & Sheets("Main" ).Cells(7, 1).Value & ", " & Year(Date) & "/" & Month(Date) & "/" & Day(Date) & ", " & Time
Selection.EntireRow.Select
Selection.Font.Bold = True
'column's heads
Range("A2" ).Select
Selection.EntireRow = a
Selection.EntireRow.Select
Selection.Font.Bold = True
'writting in the new sheet
For i = 2 To k
If Sheets("All components" ).Cells(i, 2).Value = d Then
Sheets("All components" ).Select
Range("A" & i).Select
a = Selection.EntireRow
Sheets(b).Select
Range("A" & l).Select
Selection.EntireRow = a
l = l + 1
End If
Next
End Sub
J'espère que quelqu'un pourra m'aider.
Merci.
