Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
222 views
in Technique[技术] by (71.8m points)

vba - Assigning values to variant crashes, why?

I have this cute code here, which goes through a tons of files with selecting folder and has to collect stuff to a new sheet. In this part I would like to store the header data to an array, because it is static and has to be appended to each row. The header information comes from the cellPosition array, which contains cell values like B2. Inside the for cycle I get error on save_fix_gen(i)= crashes some how at the 2nd element (i=2) and I can't figure it out why. Any help would be appreciated with a big plus!

fyi

(
    Dim save_fix_gen() As Variant, cellPositions() As Variant, i As Integer
    cellPositions() = get_general 'this comes from a function and
)
    Do While myFile <> ""

    Set ActualWorkBook = Workbooks.Open(myPath & myFile)

    ReDim save_fix_gen(0)
    ActualWorkBook.Worksheets("SheetName").Activate
    For i = LBound(cellPositions) To UBound(cellPositions)
        save_fix_gen(i) = CStr(Evaluate(cellPositions(i)))
        i = i + 1
        ReDim Preserve save_fix_gen(i)
    Next

    Loop
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

This worked, thank you. Simply set the array to the max size of the input.

ReDim save_fix_gen(UBound(cellPositions))
ActualWBK.Worksheets("Bid Leveling Template").Activate
For i = LBound(cellPositions) To UBound(cellPositions)
    save_fix_gen(i) = CStr(Evaluate(cellPositions(i)))
    i = i + 1
    'ReDim Preserve save_fix_gen(i)
Next

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...