erro 5在创建表时格式化单元格时，过程调用或参数无效

Question

在创建表时格式化单元格(使用vba(后，我开始出现一些错误。这个想法是在创建表格时格式化第一行，在第一行之后，我相信excel会自动用相同的格式类型格式化下面的行。错误显示在即时窗口中(检查下面的代码部分(

我没有尝试太多，主要是因为我不确定为什么会出现这个错误。vba格式化代码只有在创建表后才会触发，因此不能触发，因为当时没有表。我已经在上阅读了错误代码支持https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/invalid-procedure-call-or-argument-error-5我不明白我在用什么论点说我不应该。。。如果我执行一个宏并手动更改自己，它将编写一个非常相似的代码行。

Immediate window
criarTabela:5:Invalid procedure call or argument
novaLinha:5:Invalid procedure call or argument
But_Adic:5:Invalid procedure call or argument

Option Explicit
Private Sub Butao_Adicionar_Click()
On Error Resume Next
Dim forceInput(2) As String
Dim element As Variant
Dim ctCheck As Boolean
'Variaveis que indica as caixas de texto que teem ibrigatóriamente de ser preenchidas
forceInput(0) = "Input_Nome"
If Trim(Me.Input_Contacto2.Value) = "" Then
forceInput(1) = "Input_Contacto1"
Else
forceInput(1) = "Input_Contacto1"
End If
forceInput(2) = IIf(Trim(Me.Input_Local.Value) = "", "Input_Local", "Input_Localidade")
ctCheck = True
'Forçar as caixas de texto indicadas nas variaveis acima a serem preenchidas ou o utilizador será alertádo
For Each element In forceInput
If Trim(Me(element).Value) = "" Then
ctCheck = False
Me(element).BackColor = RGB(255, 255, 0) 'Colocar a caixa de texto verde
Worksheets("Dados").Cells(1, 1).Value = Join(forceInput, ",")
Else
Me(element).BackColor = RGB(255, 255, 255) 'Colocar a caixa de texto branca
End If
Next element
'Se a variavel ctCheck for true chamar a sub rotina novaLinha
If ctCheck Then
Call novaLinha
End If
If Err.Number > 0 Then
Debug.Print "But_Adic:" & Err.Number & ":" & Err.Description
End If
End Sub
Sub novaLinha()
On Error Resume Next
Dim ws As Worksheet
Set ws = Worksheets("Dados")
If Not ws.ListObjects.Count > 0 Then
Call criarTabela(ws)
End If
If ws.ListObjects.Count > 0 Then
Dim tbl As ListObject
Dim newrow As ListRow
Set tbl = ws.ListObjects("TabelaDados")
Set newrow = tbl.ListRows.Add
With newrow
.Range(1) = Me.Input_ID.Value
.Range(2) = Format(Now(), "dd/mm/yyyy")
.Range(3) = Format(Now(), "hh:mm")
criarBt .Range(4)
.Range(5) = Me.Input_Nome.Value
.Range(6) = Me.Input_Contacto1.Value & "|" & Me.Input_Contacto2.Value
End With
End If
If Err.Number > 0 Then
Debug.Print "novaLinha:" & Err.Number & ":" & Err.Description
End If
End Sub
Private Sub criarBt(cel As Range)
Dim bt As Button
Set bt = ActiveSheet.Buttons.Add(cel.Left, cel.Top, cel.Width, cel.Height)
With bt
.OnAction = "Createbutton"
.Caption = "Hora fim"
.Name = "HoraFim_" & cel.Row
End With
End Sub
Sub criarTabela(ws)
On Error Resume Next
Dim tbl As ListObject
Dim tCell As Range
Dim hdrRange As Range
Dim ctHeader() As String
Dim headerCt As Integer
Const HEADERS As String = "ID,Data,H. inicial,H. final,Nome,Contactos"
ctHeader = Split(HEADERS, ",")
headerCt = UBound(ctHeader) + 1
' Coloca os valores de HEADERS nas colunas designádas em hdrRange
Set hdrRange = Range(Cells(1, 1), Cells(1, headerCt))
hdrRange.Value = Application.Transpose(Application.Transpose(Split(HEADERS, ",")))
' Cria a tabela
Set tbl = ws.ListObjects.Add(xlSrcRange, hdrRange, , xlYes)
With tbl
.Name = "TabelaDados"
.TableStyle = "TableStyleMedium2"
.Range.AutoFilter
.Range.Cells.HorizontalAlignment = xlHAlignCenter
.Range.Cells.VerticalAlignment = xlHAlignCenter
.Range("TabelaDados[ID]").NumberFormat = "0"
.Range("TabelaDados[Data]").NumberFormat = "yyyy/mm/dd"
.Range("TabelaDados[H. inicial]").NumberFormat = "h:mm;@"
.Range("TabelaDados[H. final]").NumberFormat = "h:mm;@"
.Range("TabelaDados[Nome]").NumberFormat = "@"
.Range("TabelaDados[Contactos]").NumberFormat = "General"
End With
If Err.Number > 0 Then
Debug.Print "criarTabela:" & Err.Number & ":" & Err.Description
End If
End Sub

由于错误是在3个例程中给出的，我将它们全部放在这里。希望不要太多。这个想法是停止给出错误，并了解它们发生的原因。

Answer 1

我找到了解决问题的方法。确实做了一个小宏供人们测试。

然而，导致错误发生的行被更改为这样的行：

.Range(2, 2).NumberFormat = "yyyy/mm/dd"

上面你可以找到我做的测试代码

Sub makeTable()
Dim ws As Worksheet
Set ws = Worksheets(1)
ws.Cells(1, 1).Value = "ID"
ws.Cells(1, 2).Value = "Date"
Dim tbl As ListObject
Set tbl = ws.ListObjects.Add(xlSrcRange, Range("A1:B1"), , xlYes)
With tbl
.Name = "DataTable"
.TableStyle = "TableStyleMedium2"
.Range.AutoFilter
.Range.HorizontalAlignment = xlHAlignCenter
.Range.VerticalAlignment = xlHAlignCenter
.Range(2, 1).NumberFormat = "0"
.Range(2, 2).NumberFormat = "yyyy/mm/dd"
End With
End Sub