Excel VBA constant arrays to vba code array

Question

I'm working on excel VBA school assignment and I have an issue and can't seem to find answer online. I have database table that holds information, in certain location I have constant array of ={false, true, false} and I need it to convert it to VBA array TF(1 to 3) TF(1) should have value of false TF(2) should have value of true TF(3) should have value of false so far my code looks like this:

Sub trying()
Dim TF(1 To 3) As String
Set targetWorksheet = Worksheets("duomBazeSheet")
Worksheets("duomBazeSheet").Activate

With targetWorksheet
    TF(1) = .Cells(2, 8).Value
    MsgBox (TF(1))

End With
End Sub

"duomBazeSheet" is a sheet that has all data in it and target location is (2, 8)

any ideas?

edit: here's link how sheet looks : array is highlighted https://imgur.com/a/lII3c

what were you expecting? You are assigning a value to the array from the worksheet — QHarr
– QHarr, Commented Nov 28, 2017 at 19:51

S Meaden · Accepted Answer · 2017-11-28 20:03:01Z

2

Based on a feature called Evaluate one can do this

Dim tf
tf = Application.Evaluate("{True,False,True}")

Given the formula text in H2 try this ...

Sub Test()


    Dim tf
    tf = Application.Evaluate(ActiveSheet.Cells(2, 8).Formula)

    Dim i
    For i = LBound(tf) To UBound(tf)
        MsgBox tf(i)
    Next i

End Sub

edited Nov 28, 2017 at 20:03

answered Nov 28, 2017 at 19:55

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vygandas Eidukis Over a year ago

it's nice, sorry to post same link again, I have array highlighted : imgur.com/a/lII3c that's my issue :( it's constant array that needs to be transfered to vba it's not in different lines

Slai Over a year ago

or just tf = Evaluate([duomBazeSheet!H2].Formula)

Vygandas Eidukis Over a year ago

it gives me this :( "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score."

MoondogsMaDawg · Accepted Answer · 2017-11-28 20:09:01Z

0

The following code declares and populates a one-dimensional array with the values false, true, false. The For Loop will display each element at the end. It is unclear where you want to place these values.

Sub trying()
    Dim TF As Variant
    Dim i As Long
    Dim targetWorksheet As Worksheet

    Set targetWorksheet = Worksheets("duomBazeSheet")

    With targetWorksheet
        TF = Split(.Cells(2, 8).Value, Chr$(10)) 'Chr$(10) is a carriage return
    End With

    For i = LBound(TF) To UBound(TF)
        MsgBox TF(i)
    Next i

End Sub

edited Nov 28, 2017 at 20:09

answered Nov 28, 2017 at 19:51

MoondogsMaDawg

1,71413 silver badges22 bronze badges

6 Comments

Tom Over a year ago

Why set the array values as Strings and not Booleans?

MoondogsMaDawg Over a year ago

@Tom, agreed. I don't make the requirements, I just follow along.

Vygandas Eidukis Over a year ago

I know, but i need to get false true false, from the constant array (={false, true, false}) from the sheet here's the sheet imgur.com/a/lII3c

MoondogsMaDawg Over a year ago

Or use the " " delimiter if the array does not have commas.

Vygandas Eidukis Over a year ago

hmm, it only gets false value

|

Collectives™ on Stack Overflow

Excel VBA constant arrays to vba code array

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related