Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Array Value to Variable (VBA/Excel/2k3)

    Is it possible to create a variable from an array value? For example, if I have an array that contains the values "a", "b", "c" and "d" and I want to define "a" as a public variable with a value equal to 3, how can I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Array Value to Variable (VBA/Excel/2k3)

    Variables are defined at design time, when you write the code. Arrays only exist in runtime, when the code is executed, so you can't use them to define variables.

    If you explain what you want to accomplish, and why, we may be able to suggest a way to do that.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Array Value to Variable (VBA/Excel/2k3)

    I have several Access dbs which I access using ADO via Excel. However, I've found that there have been times in which I needed to add an additional field to the db. I would like to be able to add a field without causing a conflict in the Excel code. Currently, I create an array from Access called adoArray using .getrows and then name each value in the array using a "For" statement,

    zero = 0
    one = 1
    two = 2
    three = 3

    For i = LBound() To UBound()
    a1 = adoArray(zero, i)
    b2 = adoArray(one, i)
    c3 = adoArray(two, i)
    d4 = adoArray(three, i)
    ...
    next i

    I would like to be able to add a field to the db and the naming convention in the "For" statement to become automatic. I was thinking that I could use an countArray that included the number of fields in the db (e.g., 0,1,2,3) that the

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Array Value to Variable (VBA/Excel/2k3)

    variable names must be specified before running code.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Array Value to Variable (VBA/Excel/2k3)

    You don't need to know the names of the fields in a recordset (e.g. a table or query), you can use something like this:

    Dim rst As ADODB.Recordset
    Set rst = ...
    For i = 0 To rst.Fields.Count -1
    MsgBox rst.Fields(i).Name & ", " & rst.Fields(i).Value
    Next i

    Note: a database doesn't have fields, a table, query and recordset do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •