How to recreate variable name in excel VBA

Question

0.00/5 (No votes)

See more:

Hi,

I am writing an excel macro. Here I am trying to recreate the variable names inside loop to avoid multiple lines of almost same codes. But my problem is that the recreated variable is turning as string and I am unable to get the value assigned to that variable. Please help!!

What I have tried:

Dim days, percent As Variant
    days = Array("015", "1530", "3160", "6190", "g90")
    percent = Array("7080", "8090", "g90")

    For i = 0 To 4
        For j = 0 To 2
            v = cnt7080_015
            valu = "cnt" & percent(j) & "_" & days(i)
            
            Cells(rowc, k).Value = "cnt" & percent(j) & "_" & days(i)
            rowc = rowc + 1
        Next
        k = k + 1
        rowc = 7
    Next

Posted 24-Apr-17 0:59am

planetz

Updated 24-Apr-17 13:59pm

Add a Solution

Comments

Richard MacCutchan 24-Apr-17 7:16am

Your code is doing exactly what you tell it. It sets a cell value to a string.

planetz 24-Apr-17 7:38am

But how to convert it to variable name?

ZurdoDev 24-Apr-17 7:45am

Pretty sure you use Eval(). Eval("cnt" & percent(j) & "_" & days(i))

Patrice T 24-Apr-17 12:37pm

Where those variables are coming from ?

planetz 25-Apr-17 1:12am

The variables are defined at the start and in course of program values are assigned to them through calculation. In the end I am trying to get those values back from them and paste them in cells.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-04-24T05:17:00

Eval works in Access but does not exist in Excel VBA.

There are some workarounds on this post Using string to reference a variable (Excel Office 2003) | Windows Secrets Lounge[^] . My personal preference has always been to use a worksheet and step through the rows as required (although to be perfectly honest my personal preference is to have nothing to do with Excel VBA - but that's just me)

Maciej Los · Answer 2 · 2017-04-24T05:17:00

Take a look at code after small changes:

VB

Dim days As Variant, percent As Variant, sTmp As String
Dim wsh As Worksheet 'declare variable which refers to Sheet1 

'initiate variable of type Worksheet
'change it to your needs
Set wsh = ThisWokbook.Worksheets("Sheet1")

    days = Array("015", "1530", "3160", "6190", "g90")
    percent = Array("7080", "8090", "g90")
 
    For i = LBound(days) To UBound(days)
        For j = LBound(percent) To UBound(percent)
            'v = cnt7080_015
            sTmp = "cnt" & percent(j) & "_" & days(i)
            wsh.Cells(rowc, k).Value = sTmp
            rowc = rowc + 1
        Next
        k = k + 1
        rowc = 7
    Next

'clean up!
Set wsh = Nothing

I'd strongly recommend to read this: Coding Techniques and Programming Practices[^]

Patrice T · Answer 3 · 2017-04-24T13:59:00

Solution 3

Your problem look like a bad solution to a wrong design.
My understanding is that you try to access VBA variables by compounding their names.

Why are you using 15 variables and not a 2D array instead ?
[Update]

Quote:
Will you help me understand how can I define the array so that the output is not a string?

Excel VBA Programming - Multidimensional Arrays[^]

Posted 24-Apr-17 13:59pm

Patrice T

Updated 24-Apr-17 20:53pm

v3

Comments

planetz 25-Apr-17 2:02am

Will you help me understand how can I define the array so that the output is not a string?

How to recreate variable name in excel VBA

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0