vba 使用变量代替列名或数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5191539/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using variables instead of column names or numbers
提问by Ashok Jeev
I'm trying to draw a chart in Excel and here is my code
我正在尝试在 Excel 中绘制图表，这是我的代码
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'ResultHL'!$E:$E")
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = "='ResultHL'!$CB:$CB20"
This is working fine but I'd like t know whether i can assign column names or numbers to some variables and use it in the above code.
这工作正常，但我想知道我是否可以将列名或数字分配给某些变量并在上面的代码中使用它。
In line 2--> ActiveChart.SetSourceData Source:=Range("'ResultHL'!$E:$E")i need to assign E to a variable and use it
在第 2 行-->ActiveChart.SetSourceData Source:=Range("'ResultHL'!$E:$E")我需要将 E 分配给一个变量并使用它
also in line 5, i need to use an integer variable instead of 2520
同样在第 5 行，我需要使用整数变量而不是 2520
How do I do that?
我怎么做？
回答by Jean-Fran?ois Corbett
I find it's much easier to use the Cellsand/or Resizeproperties than to get bogged down in all this messy string concatenation business.
我发现使用Cells和/或Resize属性比陷入所有这些凌乱的字符串连接业务要容易得多。
Example:
例子：
Dim rngApples As Range
Dim shtResults As Worksheet
Dim lngMaxRow As Long
shtResults = Worksheets("Sheet1")
' Define the range you want to plot
lngMaxRow = 2520
rngApples = shtResults.Range("CB1").Resize(lngMaxRow, 1)
ActiveChart.SeriesCollection(2).Values = rngApples
Alternatively, you can define your range as follows:
或者，您可以按如下方式定义范围：
lngColNum = 56 ' or whatever CB is
lngMaxRow = 2520
rngApples = shtResults.Range(Cells(1, lngColNum), Cells(lngMaxRow, lngColNum))
回答by Bravax
The cell & range specifiers are just strings, so you can concantenate them with strings or numbers to reference the cells you want.
单元格和范围说明符只是字符串，因此您可以将它们与字符串或数字连接起来以引用所需的单元格。
For example:
ActiveChart.SetSourceData Source:=Range("'ResultHL'!$E:$E")
Could become
ActiveChart.SetSourceData Source:=Range("'ResultHL'!$" & ColumnName & ":$" & ColumnName)
Where ColumnName is the string value of the column you wish to use.
And
和
ActiveChart.SeriesCollection(2).Values = "='ResultHL'!$CB:$CB20"
Could become
ActiveChart.SeriesCollection(2).Values = "='ResultHL'!$CB:$CB$" & MaxRowNumber
