Wrox Programmer Forums
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 16th, 2007, 05:57 AM
Registered User
Join Date: Aug 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Simple question

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

This is a small piece of the code:

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

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

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)

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

Old August 16th, 2007, 02:22 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

    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
    Dim rol1 As String, rol2 As String, rol3 As String, _
        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.
    Dim rol(1 To 6) As String
        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)
Old August 20th, 2007, 06:00 AM
Registered User
Join Date: Aug 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

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

But the application works...very nice.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Very Simple Question YoungLuke C# 2 May 4th, 2007 02:23 AM
Simple Question ironchef Java GUI 0 September 14th, 2006 04:56 PM
Simple Question dpkbahuguna Java Basics 2 May 19th, 2006 12:05 AM
Simple question happyheart_man Pro VB.NET 2002/2003 0 January 14th, 2004 11:38 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.