vba - Private Sub User-Defined Type Not Defined Range Sheet

Question

第一篇文章。我有下面相对简单的代码，并且得到了

User-defined type not defined

错误。我知道当我将独立代码放入一个 Sub 时，它可以工作，但由于各种原因，我想将其拆分，以便在我更大的工作簿中，我可以调用第二个 sub 而不必复制和粘贴整个循环多个次。代码的目的是在 excel 中自动调整指定范围。

Sub letsGo()
Dim rng As Range
Dim sht As Worksheet
Set rng = ThisWorkbook.Sheets("Sheet1").Range("Range1")
Set sht = ThisWorkbook.Sheets("Sheet1")
Call whyDoesntThisWork(sht, rng)
End Sub

Private Sub whyDoesntThisWork(rangeSheet As Sheet, rangeTable As Range)
Dim Col As Range
Dim reSize As Range
For Each Col In rangeTable.Columns
    If Col.Hidden = False Then
        Set reSize = rangeSheet.Range(rangeSheet.Cells(rangeTable.Row, Col.Column), rangeSheet.Cells(rangeTable.Rows.Count, Col.Column)) reSize.Columns.autoFit
    End If
Next Col
End Sub

Answer 1

您有两种不同的数据类型:

Private Sub whyDoesntThisWork(rangeSheet As Sheet, rangeTable As Range)

rangeSheet 是一个工作表，但是当您调用它时，您会通过:

Call whyDoesntThisWork(sht, rng)

sht is of type WorkSheet

那是你的不一致。我建议您将定义更改为:

Private Sub whyDoesntThisWork(rangeSheet As WorkSheet, rangeTable As Range)