【原】Excel VBA 9.4 数组写入excel的方法和技巧

Excel和VBA 2022-05-14

展开全文

点击上方“Excel和VBA”，选择“置顶公众号”

致力于原创分享Excel的相关知识，源码，文件打包提供

一起学习，一起进步~~

之前学习了数组的一些简单的知识，今天我们继续，今天我们来学习下数组的写入

当我们通过各种方式获得了一个数组之后，我们最终的目的，是要将他们写入Excel中，将它们展示出来

那么这个时候又来了一个问题，如何将数组写入Excel中呢？

场景1

在之前我们学习将Excel的数据写入数组的时候，可以直接用"="来实现

比方说这样

Sub test()
arr = ActiveSheet.UsedRange
End Sub

那么是不是在我们写入的时候，也可以直接倒过来用呢？

Sub test()
arr = ActiveSheet.UsedRange
Cells(1, 11) = arr
End Sub

理想很美好，但是现实并不这样，从截图中来看，虽然是写入了数据，但是有且仅有写入了一个数据

那我们来仔细的分析下，虽然是写入了一个数据，但是我们从这个数据出发，这个数据是只有一个，但是是不是我们的单元格区域不对呢？

我们是不是需要提供一个完整的单元区域，才可以成功的写入呢？

那我们来试下，我们尝试来提供一个完整的单元格区域

数组其实本身并不难，难就难在空间逻辑思维的构造，如何在脑海中形成一个动态数组的空间概念，这还需要大家在之后多学习多练习，最终才可以水到渠成

Sub test()
arr = ActiveSheet.UsedRange
Range(Cells(1, 11), Cells(100, 100)) = arr
End Sub

数据虽然是成功的写入了，但是又有新问题了，那就是这个区域超出了原来的数据的范围，那么其他的超出的范围就会写入错值，这样的结果很明显不是我们想要的。

从这里来分析，我们在写入数据的时候，需要保证单元格的区域，一定要和数据的维度一致

那么我们现在换种方法，我们尝试去构造一个单元区域，这个区域要和数组的维度完全一致才可以

这里我们利用数组的最大下标来构造这个

Sub test()
arr = ActiveSheet.UsedRange
Cells(1, 11).Resize(UBound(arr), UBound(arr, 2)) = arr
End Sub

现在再来看看，这个结果就完美很多了。

这里我们是利用数组的UBound和单元格的Resize方法，构造一个和数组的维度相同的那么一个单元格区域，然后再将数据写入的，这样的结果刚好，非常完美

我们来看看下一个场景

场景2

现在我们希望将这个数据源中，所有含有1的数据提取出来装到一个数组中，那么这个时候我们要如何来处理呢？

看下代码

Sub ress()
Dim rng As Range, arr()
k = 1
For Each rng In ActiveSheet.UsedRange
    If rng Like "*1*" Then
        ReDim Preserve arr(1 To 1, 1 To k)
        arr(1, k) = rng.Value
        k = k + 1
    End If
Next rng
Cells(1, 11).Resize(1, UBound(arr, 2)) = arr
End Sub

这里呢，我们可以看到结果全部展示在了一行，这样的结果，查阅并不方便，如果数据量很大的话，一行展示，看起来很不方便，所以这个时候我们肯定希望在一列展示，那这个时候我们要如何改？

还是在单元格区域改吗？我只需要一列，那我就展示一列就可以了呀

Sub ress()
Dim rng As Range, arr()
k = 1
For Each rng In ActiveSheet.UsedRange
    If rng Like "*1*" Then
        ReDim Preserve arr(1 To 1, 1 To k)
        arr(1, k) = rng.Value
        k = k + 1
    End If
Next rng
Cells(1, 11).Resize(k, 1) = arr
End Sub

看起来好像是实现了效果，但是实际上呢？我们已经最数据源做出了一些改动

并不是所有的结果都是含有1,12,13，这种都算是含有1，那么在这个时候我们看到，其实他写入的结果似乎不正确，一直都是一个test1，但是其他的结果都没有展示，这样是不对的，

因为他写入都是数组的第一个数据

为什会这样呢？

这里我们就需要学习一个新的概念，那就是数组的转置，所谓的数组的转置，就是相当于将数组的维度进行转换，建一个一维的数组，转换成为一个多维，再说得直白一点，那就是将一行转成为一列

如何实现呢，很简答，将两个单词就可以实现了。

我们现在再来看看新的代码

Sub ress()
Dim rng As Range, arr()
k = 1
For Each rng In ActiveSheet.UsedRange
    If rng Like "*1*" Then
        ReDim Preserve arr(1 To 1, 1 To k)
        arr(1, k) = rng.Value
        k = k + 1
    End If
Next rng
Cells(1, 11).Resize(k - 1, 1) = WorksheetFunction.Transpose(arr)
End Sub