如何使Excel电子表格范围存储VBA变量?

Okay so I have spent.. I'll just say.. like an hour (sadly I am lying, its been like a whole week) trying to figure this out. And I can't figure it out godd*mnit >_<

好吧，我花了。我想说. .就像一个小时(很遗憾，我在撒谎，整个星期都在撒谎)试图弄明白这一点。我不能算出godd*mnit >_<

Assume we have a text file in notepad (_A_File_.txt) containing just 4 lines:

假设在记事本(_A_File_.txt)中有一个文本文件，仅包含4行:

ACCOUNT NUMBER: 123456789    '(line 2)
SHORT NAME: JON SMITH        '(line 2)
ACCOUNT NUMBER: 987654321    '(line 3)
SHORT NAME: BOB BARKER       '(line 4)

Let's assume that within an excel spreadsheet the below:

让我们假设在excel电子表格中:

Range C1 = "actNumberTrim"
Range C2 = "shortNameTrim"
Range C3 = "actNumberTrim"
Range C4 = "shortNameTrim"

Lets also assume the below VBA script:

我们还假设以下VBA脚本:

Sub obey_me_you_stoopit_code_()

Dim actNumber As Integer
Dim shortName As Integer
Dim actNumberTrim As String
Dim shortNameTrim As String
Dim trimArray1 As Variant
Dim myFile As String
Dim text As String
Dim textline As String


actNumber = InStr(text, "ACCOUNT NUMBER: ") 'THERE IS A SPACE BETWEEN : AND "
shortName = InStr(text, "SHORT NAME: ") 'THERE IS A SPACE BETWEEN : AND "
actNumberTrim = Mid(text, actNumber + 16, 10)
shortNameTrim = Mid(text, shortName + 12, 10)


myFile = "C:\Users\Bob\Desktop\_A_File_.txt"

Open myFile For Input As #1
Do Until EOF(1)

    Line Input #1, textline
    text = text & textline
    Loop

trimArray1 = ThisWorkbook.Worksheets("sheet1").Range("C1:C4")


For i = 1 To UBound(trimArray1)

 MsgBox trimArray1(i, 1)

Next i
Close #1
End Sub

the output I get is:

我得到的输出是:

[ actNumberTrim ]
[ shortNameTrim ]
[ actNumberTrim ]
[ shortNameTrim ]

How would I go about fixing the above so that values in Ranges C1:C4 are treated as:

我该如何解决上述问题，使C1:C4范围内的值被视为:

Variables and not, say.. pointless text in a cell
变量,而不是说. .单元格中无意义的文本。

So that the output returned is:

因此返回的输出为:

[ 123456789 ]
[ JON SMITH ]
[ 987654321 ]
[ BOB BARKER ]

I've tried changing the data types of actNumberTrim and shortNameTrim. It didn't work All I got was this:

我尝试过修改actNumberTrim和shortNameTrim的数据类型。它不管用我得到的是

Run-Time Error: "Your code sucks brah"

运行时错误:“您的代码很糟糕”

Thoughts?

想法吗?

EDIT 1 (Attempt using Microsoft Scripting Runtime)

编辑1(尝试使用Microsoft脚本运行时)

    Dim trimArray1 As Variant
    Dim myFile1 As String
    Dim text As String
    Dim textline As String
    Dim myDict As New Dictionary

    myFile1 = "C:\Users\BOB\Desktop\_A_File_.txt"
    Open myFile1 For Input As #1
    Do Until EOF(1)

        Line Input #1, textline
        text = text & textline
        Loop
    Close #1
    trimArray1 = ThisWorkbook.Worksheets("sheet22").Range("C1:C4")
    v = Split(textline, ":") 'where v has been declared to be variant
    myDict.Add Trim(v(0)), Trim(v(1))
    For i = 1 To UBound(trimArray1)

    MsgBox myDict(trimArray1(i, 1))

    Next i


    [ BLANK ]
    [ BOB BARKER ]
    [ BLANK ]
    [ BOB BARKER ]

Cells C1:C4 Contained:

细胞C1:C4包含:

    ACCOUNT NUMBER
    SHORT NAME
    ACCOUNT NUMBER
    SHORT NAME

Not sure what I am doing wrong or if I have overlooked something. How would I use this method for fields that don't have ":" separating them? The version of the text file that I had posted was a little oversimplified. Feedback?

不知道自己做错了什么，或者是否忽略了什么。我如何将此方法用于没有分隔它们的字段?我发布的文本文件的版本有点过于简化了。反馈?

EDIT 2 Below was my original approach

下面的编辑2是我最初的方法

Below is an example of two full records within an example text file:

下面是一个示例文本文件中两个完整记录的示例:

    ACCOUNT ABCDEF12                                                                 
    CUSTOMER NAME:    JOHN B. SMITH         CSA REP:  154983                   
    ACCOUNT OPEN:     05/10/15
    CUSTOMER ADDRESS: 123 SOMEWHERE DRIVE   SOMETHING HERE:                   
    LAST ORDER:       06/24/2011             COUNTRY CODE: UNITED STATES      
    INVOICE #:        123456789             STATE CODE:    CALIFORNIA         
    LAST MAINTENANCE: 01/02/15               COUNTY CODE:  UNCODED            
    SOME INDICATOR:   NO   COMPLAINTS: NO   IPM IND:       DATAPREP/PERF4     
    SOME INDICATOR:   NO STATUS:  NONE      AUTO RENEW:    YES                
    SOMETHING HERE   NO                             
    SOMETHING HERE:          ABC IND:       
    SOMETHING HERE   2    ABC ASSET NO:  T                                           

    ACCOUNT ABCDEF12                                                                 
    CUSTOMER NAME:    JOHN B. SMITH         CSA REP:  154983                   
    ACCOUNT OPEN:     05/10/15
    CUSTOMER ADDRESS: 123 SOMEWHERE DRIVE   SOMETHING HERE:                   
    LAST ORDER:       06/24/2011             COUNTRY CODE: UNITED STATES      
    INVOICE #:        123456789             STATE CODE:    CALIFORNIA         
    LAST MAINTENANCE: 01/02/15               COUNTY CODE:  UNCODED            
    SOME INDICATOR:   NO   COMPLAINTS: NO   IPM IND:       DATAPREP/PERF4     
    SOME INDICATOR:   NO STATUS:  NONE      AUTO RENEW:    YES                
    SOMETHING HERE:   NO                             
    SOMETHING HERE:          ABC IND:       
    SOMETHING HERE:   2    ABC ASSET NO:  T

Below was the way I had originally coded it:

下面是我最初编码的方式:

'
    Dim myFile As String
    Dim text As String
    Dim textline As String
    Dim cstAct as integer
    Dim actOpe as integer
    Dim cusNam as integer
    Dim act as integer
    Dim reg as integer

    myFile = "put file patch to text file here"
    myFile = Application.GetOpenFilename()

    Do Until EOF(1)
    Line Input #1, textline
    text = text & textline
    Loop

    cusAct = InStr(text, "ACCOUNT ")
    actOpe = InStr(text, "ACCOUNT OPEN:")
    reg = InStr(text, "REGION:")
    cusNam = InStr(text, "CUSTOMER NAME:")

    For i = 2 To ThisWorkbook.Worksheets("b2").Range("a65536").End(xlUp).Row
    ThisWorkbook.Worksheets("name").Range("a" & i).Value = Mid(text, act + 6, 9)
    ThisWorkbook.Worksheets("name").Range("b" & i).Value = Mid(text, cstAct + 6, 9)
    ThisWorkbook.Worksheets("name").Range("c" & i).Value = Mid(text, actOpe + 13, 27)
    ThisWorkbook.Worksheets("name").Range("d" & i).Value = Mid(text, cusNam  + 20, 19)

    next i

    'Format and autofit                         
    For x = 2 To ThisWorkbook.Worksheets("b2").Range("a65536").End(xlUp).Row
    Range("a" & x).Value = Application.WorksheetFunction.Clean(trim(Range("a" & x)))
    Range("b" & x).Value = Application.WorksheetFunction.Clean(trim(Range("b" & x)))
    Range("c" & x).Value = Application.WorksheetFunction.Clean(trim(Range("c" & x)))
    'etc etc
    next x

1 个解决方案

#1

You can use strings as keys to a dictionary (which act sort of like variables which are linked to the corresponding values). In the VBA editor under Tools/References, include a reference to Microsoft Scripting Runtime. Then at the top of your code have a line like

您可以使用字符串作为字典的键(它的作用有点像与相应值相关联的变量)。在工具/引用下的VBA编辑器中，包含对Microsoft脚本运行时的引用。然后在代码的顶部有一行类似的代码

Dim myDict As New Dictionary

Then, when you loop through the file and load up the variable textline by the successive lines, don't tack it onto the end of a big variable but instead have the lines

然后，当您循环遍历文件并通过连续的行加载变量textline时，不要将其添加到一个大变量的末尾，而是使用这些行。

v = Split(textline, ":") 'where v has been declared to be variant
myDict.Add Trim(v(0)), Trim(v(1))

Fix the cells in column C so that they coincide with the actual (trimmed) strings in the text file before the colon. No need to eliminate spaces - "Account Number" is a perfectly valid cell value and a perfectly valid dictionary key. Later, when you are looping through the values drawn from column C, replace

修复列C中的单元格，使它们与冒号之前的文本文件中的实际(修剪过的)字符串一致。不需要删除空格——“Account Number”是一个完全有效的单元格值和一个完全有效的字典键。稍后，当您循环遍历列C中绘制的值时，请替换

MsgBox trimArray1(i, 1)

通过

MsgBox myDict(trimArray1(i, 1))

This should (if I understand your intentions correctly) do what you want it to do.

这应该(如果我理解正确的话)做你想做的。

#1

Dim myDict As New Dictionary