Simple question

VBAprutser · August 16th, 2007, 05:57 AM

I'm creating a handy tool for my work. I use Excel with VBA.

This is a small piece of the code:

FIRST PART:
Dim rol1, rol2, rol3, rol4, rol5, rol6 As String
aantalrollers = 6
rol1 = "Piet"
rol2 = "Mike"
rol3 = "Sandra"
rol4 = "Eveline"
rol5 = "Wim"
rol6 = "RFN"

SECOND PART:
For k = 1 To aantalrollers
ws0.[B1].Offset(0, k - 1).Value = rol & k
Next

The second part is not right, i know. This is my plan in short words:
How can i let VBA see rol & k (with k=1 to ..) as THE VALUE of rol1 to rol..?

More explained:
Rol1 to rol6 are names of colleages. I want to have their names written in excel. But when the numbers or names change, i only want to adjust the first part (maybe inputbox, userform). The second part is used deep in the rest of the code.
What i want:
in cell B1: the name of rol1 (Piet)
in cell B2: the name of rol2 (Mike)
..etc

I'm sure there is a simple solution, but i can't find it :)
Thanx

BrianWren · August 16th, 2007, 02:22 PM

Code:

    Dim rol1, rol2, rol3, _
        rol4, rol5, rol6 As String

makes 5 Variants, and 1 string. (The method you used works in C and in VB.NET.) You need to use

Code:

    Dim rol1 As String, rol2 As String, rol3 As String, _

Code:

        rol4 As String, rol5 As String, rol6 As String
    ' or
    Dim rol1 As String
    Dim rol2 As String
    Dim rol3 As String
    Dim rol4 As String
    Dim rol5 As String
    Dim rol6 As String

To do this you must make an array, and use k as the index. You cannot use dynamic variable names. After compilation variables do not have names at all. During the compilation process, the names are used to load a token table, which is then incorporated into the .EXE. Therefore there is nothing to find through concatenating rol with the value of k.

Code:

    Dim rol(1 To 6) As String

Code:

        rol(1) = "Piet"
        rol(2) = "Mike"
        rol(3) = "Sandra"
        rol(4) = "Eveline"
        rol(5) = "Wim"
        rol(6) = "RFN"

    For k = 1 To UBound(rol)
        ws0.[B1].Offset(0, k - 1).Value = rol(k)
    Next

VBAprutser · August 20th, 2007, 06:00 AM

Thanx a lot. I never thought of using an array (i'm a professional beginner

)

But the application works...very nice.