如何将字符串类型数组转换为变量类型数组-Excel VBA [英] How to convert string type array to variant type array - Excel VBA

问题描述

我正在尝试将以制表符分隔的文本文件中存储的公式加载到工作表中的某个范围.我已经使用函数Split(Expression As String, Delimiter)正确地将每一行依次正确加载到一维数组中，但是遇到了有关返回的数组类型的问题.

I am attempting to load formulae stored in a tab-delimited text file into a range in a worksheet. I have used the function Split(Expression As String, Delimiter) to correctly load each line in turn into a 1D array, but have run into problems concerning the array type returned.

Split函数仅返回字符串类型的数组，而我需要一个变量类型数组来将范围设置为.这是因为使用字符串类型数组设置单元格的公式会导致将单元格值设置为原始文本，即使字符串以等号开头.

The Split function only returns string type arrays, and I need a variant type array to set the range to. This is because setting the formulae of cells using a string type array causes the cell values to be set to the raw text, even if the strings begin with an equals sign.

'Example code to demonstrate the problem:
Sub Tester()
    Dim StringArr(1 To 3) As String
    StringArr(1) = "= 1"
    StringArr(2) = "= 2"
    StringArr(3) = "= 3"
    Range("Sheet1!$A$1:$C$1").Formula = StringArr
    'Cells display raw string until edited manually

    Dim VariantArr(1 To 3) As Variant
    VariantArr(1) = "= 1"
    VariantArr(2) = "= 2"
    VariantArr(3) = "= 3"
    Range("Sheet1!$A$2:$C$2").Formula = VariantArr
    'Cells display formula result correctly
End Sub

结果输出:

我想知道是否有一种方法可以将从Split函数返回的数组转换为变量类型数组，最好没有循环.我知道我可以在一个循环中分别设置每个单元格公式，但是我试图使其保持尽可能高效和整洁.

I would like to know if there is a way to convert the array returned from the Split function to a variant type array, preferably without loops. I am aware that I could set each cell formula individually within a loop, but I am trying to keep it as efficient and neat as possible.

我正在使用Microsoft Excel for Mac 2011 14.5.5版. VBA使我无所适从.

I am using Microsoft Excel for Mac 2011, Version 14.5.5. VBA is driving me up the wall.

推荐答案

您可以使用

You can use WorksheetFunction.Index (or Application.Index) to convert the array of String to array of Variant/String:

Sub Test()

    StringArr = Split("=1 =2 =3")
    VariantArr = WorksheetFunction.Index(StringArr, 1, 0)
    Range("Sheet1!$A$1:$C$1").Formula = StringArr
    Range("Sheet1!$A$2:$C$2").Formula = VariantArr

End Sub

以下是数组类型:

预期输出:

这篇关于如何将字符串类型数组转换为变量类型数组-Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！