WorksheetFunction.Transpose改变了数据类型。

Question

我在VBA中使用WorksheetFunction.Transpose将一个混合日期/字符串的1D数组转换为2D数组以便写入工作表。

设置了Windows区域设置为DMY后，写回的日期被交换了月份/日期。

- Ron Rosenfeld

Answer 1

在本论坛中提到了这个问题，涉及到将Dates转换为Strings，这是通过WorksheetFunction.Transpose方法实现的。

我深入研究了一下这个问题。

似乎WorksheetFunction.Transpose可以转换许多数据类型。转置后的结果可能是Boolean、Double或者String中的任意一种。

使用VBA时需要考虑到这一点。

以下是一些演示此问题的代码：

Option Explicit
Option Base 1
Sub Tester()
    Dim v, w, i As Long
    
v = Array(CBool(-1), CBool(0), CByte(9), CDbl(1234), CDec(1234), _
            CInt(1234), CLng(1234), CLngPtr(1234), CSng(1234), _
            CCur(123456), #5/1/2015#, "1234")
            
w = WorksheetFunction.Transpose(v)

For i = 1 To UBound(v)
    Debug.Print v(i), TypeName(v(i)), w(i, 1), TypeName(w(i, 1))
Next i


End Sub

debug.print输出

True          Boolean       True          Boolean
False         Boolean       False         Boolean
 9            Byte           9            Double
 1234         Double         1234         Double
 1234         Decimal        1234         Double
 1234         Integer        1234         Double
 1234         Long           1234         Double
 1234         Long           1234         Double
 1234         Single         1234         Double
 123456       Currency      $123,456.00   String
01-May-15     Date          01-05-2015    String
1234          String        1234          String

编辑 WorksheetFunction.Transpose 的另一个问题

• 给定一个一维数组
• 转置数组的第一维上限将由公式 Ubound(1D_array) mod 2^16 给出
• 只有前 uBound(1D_array) mod 2^16 个元素将返回到转置后的数组中。
  • 因此，如果一维数组的上限为65537，则转置后的数组将只包含一个项目（原始数组中的第一个项目）
  • 不会返回错误消息。
    • 我认为缺乏错误消息和这种行为始于Excel 2013。我记得早期版本在这种情况下会返回错误消息。

- Ron Rosenfeld

---

那么有什么解决方法？手动创建第二个数组并循环填充吗？ - jamheadart

@jamheadart 如果您在主数组中使用了字符串、双精度或布尔等以外的数据类型，那么这是我所知道的唯一解决方案。不过您可以将日期存储为双精度，这样在转置时其值不会改变。 - Ron Rosenfeld

如果初始数组是从日期范围中填充的，则可以尝试使用 Range.Value2 而不是 Range.Value。它会保留底层值。 - shrivallabha.redij

@shrivallabha.redij 是的，正如我所写的那样，这将把日期存储为双精度浮点数。 - Ron Rosenfeld

@VBasic2008，你看到的结果与我发布的不符吗？ - Ron Rosenfeld

Answer 2

您可以在内存中使用 ListBox 进行转置，而无需使用 Transpose，并且数据类型保持不变：

Option Explicit
Option Base 1
Sub Tester2()
    Dim v, w, i As Long
    
v = Array(CBool(-1), CBool(0), CByte(9), CDbl(1234), CDec(1234), _
            CInt(1234), CLng(1234), CLngPtr(1234), CSng(1234), _
            CCur(123456), #5/1/2015#, "1234")
            
w = WorksheetFunction.Transpose(v)

For i = 1 To UBound(v)
    Debug.Print v(i), TypeName(v(i)), w(i, 1), TypeName(w(i, 1))
Next i
   
    With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
    'Listbox always Base 0
        .List = v
         w = .List 'Now 2-dim with correct typenames, use .column for transpose Row-column)
    End With

For i = 1 To UBound(v)
    Debug.Print v(i), TypeName(v(i)), w(i - 1, 0), TypeName(w(i - 1, 0))
Next i

End Sub

- EvR

---

谢谢，但我发帖的原因是想指出我遇到的问题，这样其他人就可以意识到它了。有很多变通方法 - 但如果你不知道可能会发生什么，你就不会使用它们，最终不得不处理看似无法解释的错误。我没有考虑ListBox方法，但它似乎可行。然而，在一个包含100,000个日期数据的样本中，ListBox方法需要3-4倍于简单循环的时间。（在原始数据样本中也需要更长的时间）。 - Ron Rosenfeld