如何在即时窗口中打印一个范围变量？Excel VBA

Question

我正在尝试做一件非常简单的事情。但是由于今天才开始学习，所以还不能完全理解。

这是我目前的代码：

Public Sub getCellData()
   Dim wb As Workbook: Set wb = ThisWorkbook
   Dim ws As Worksheet: Set ws = wb.Sheets(1)
   Dim rng As Range: Set rng = ws.Range(Cells(1, 2), Cells(4, 2))

   Debug.Print rng
End Sub

我正在处理的数据是这样的：

我一直收到“运行时错误'13'：类型不匹配”的提示。我在谷歌上搜索了这个错误，但仍然不确定如何修复它。我想在立即窗口中打印变量rng。

- AP1

---

你是想打印范围内的“值”吗？ - John Coleman

@JohnColeman 是的，范围内的值。 - AP1

Answer 1

你可以编写一个简单的子程序来实现类似这样的功能：

Sub PrintRange(R As Range, Optional delim As String = ", ")
    Dim myRow As Range, V As Variant, i As Long
    For Each myRow In R.Rows
        ReDim V(1 To myRow.Cells.Count)
        For i = 1 To myRow.Cells.Count
            V(i) = myRow.Cells(1, i).Value
        Next i
        Debug.Print Join(V, delim)
    Next myRow
End Sub

然后，PrintRange rng 将按预期工作。

- John Coleman

Answer 2

Range 是一个对象，而不是一个值。要输出值，可以迭代 Range。另一种方法是在单行或单列上使用 Transpose 函数，然后使用 Join 获取 Range 中数值数组的 String 值。

示例代码：

Public Sub getCellData()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Sheets(1)

    ' you need to prefix Cells with ws. to clarify the reference
    Dim rng As Range: Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(4, 2))

    ' you cannot debug print the object itself
    'Debug.Print rng

    ' iterate the range
    Dim rngCell As Range
    For Each rngCell In rng
        Debug.Print rngCell.Value
    Next rngCell

    ' use the Transpose function for a single row or column
    Dim strData As String
    Dim wsf As WorksheetFunction: Set wsf = Application.WorksheetFunction
    strData = Join(wsf.Transpose(rng.Value), ",")
    Debug.Print strData


End Sub

注意，我已将您的Set rng = ...更新为：

Set rng = ws.Range(ws.Cells(1, 2), ws.Cells(4, 2))

为了明确定义引用，将Cells前缀添加为ws.。

- Robin Mackenzie

---

谢谢。这对我有用。感谢你的帮助 :) - AP1