如何在Excel VBA中重新创建变量名称 [英] How to recreate variable name in excel VBA

问题描述

我正在写一个excel宏。在这里，我试图在循环内重新创建变量名称，以避免多行几乎相同的代码。但我的问题是重新创建的变量转为字符串，我无法获得分配给该变量的值。请帮助!!



我尝试过：

昏暗天数，百分比As Variant 
 days = Array（015，1530，3160，6190，g90）
 percent = Array（7080， 8090，g90）
 
对于i = 0到4 
对于j = 0到2 
  v = cnt7080_015 
 valu =cnt &百分比（j）& _& days（i） 
 
 Cells（rowc，k）.Value =cnt&百分比（j）& _&天（i）
 rowc = rowc + 1 
下一个
k = k + 1 
 rowc = 7 
下一个

解决方案

Eval 适用于Access，但在Excel VBA中不存在。



这篇文章有一些解决方法使用字符串引用变量（Excel Office 2003）| Windows Secrets Lounge [ ^ ]。我个人的偏好一直是使用工作表并根据需要逐步执行（虽然说实话，我个人的偏好是与Excel VBA无关 - 但这只是我）

< blockquote>在稍作修改后查看代码：

  Dim 天作为 变体，百分比作为 变体，sTmp 作为 字符串 
  Dim  wsh 作为工作表' 声明变量它引用Sheet1  
 
 ' 启动工作表类型的变量 
 ' 更改它符合您的需求 
 设置 wsh = ThisWokbook.Worksheets（  Sheet1）
 
 days = Array（  015，  1530，  3160，  6190 ，  g90）
 percent = Array（  7080，  8090，  g90）
 
 < span class =code-keyword>对于 i = LBound（天）到 UBound（天）
 对于 j = LBound（每个分）  UBound（百分比）
 '  v = cnt7080_015  
 sTmp =   cnt&百分比（j）&   _&天（i）
 wsh.Cells（rowc，k）.Value = sTmp 
 rowc = rowc +  1  
 下一步 
k = k +  1  
 rowc =  7  
 下一步 
 
 ' 清理！ 
 设置 wsh = 没什么 

我强烈建议您阅读：编码技巧和编程实践 [ ^ ]

您的问题看起来像是错误设计的错误解决方案。 />
我的理解是你尝试通过复合它们的名字来访问VBA变量。



你为什么使用15个变量而不是2D数组呢？

[更新]

引用：

你能帮我理解如何定义数组，使输出不是字符串吗？

Excel VBA编程 - 多维数组 [ ^ ]

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

解决方案

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)

Take a look at code after small changes:

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[^]

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[^]

这篇关于如何在Excel VBA中重新创建变量名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！