Excel VBA运行时错误1004仅以“c”开头的名称 [英] Excel VBA runtime error 1004 only with names that begin with 'c'
问题描述
没错。如果我将以下代码中的Chart_Series_W_Gain_AAPL中的C更改为代码工作的任何其他字母。否则,它会在Series.Formula作业中抛出一个错误1004。事实上,如果我使用以'c'开头的任何随机名称,代码将失败,但不是其他的。我已经尝试关闭Excel并重新打开,但同样的问题。我遇到这个是因为我以图表的名字命名我的图表系列定义的名字,但是后来我决定这是令人困惑的,我试图用Chart_Series_作为图表系列添加定义的名称。相当温和的变化,人们会想。
That's right. If I change the 'C' in 'Chart_Series_W_Gain_AAPL' in the following code to any other letter the code works. Otherwise it throws an Error 1004 at the Series.Formula assignment. In fact, if I use any random name that begins with 'c' the code fails but not otherwise. I've tried closing Excel and reopening, but same issue. I came across this because I had been naming my chart series defined names beginning with the name of the chart, but then I decided that was confusing and I attempted to prepend defined name used as a chart series with "Chart_Series_". Pretty benign change, one would think.
Dim objChartWGain As Chart
Dim objSeries As Series
Set objChartWGain = Charts("W Gain")
Set objSeries = objChartWGain.SeriesCollection.NewSeries
ActiveWorkbook.Names.Add "Chart_Series_W_Gain_AAPL", "=W_Gain_Data_Array(W_Gain_Data_Alloc,1,W_Gain_Data_GainLossCurr)"
objSeries.Formula = "=SERIES(""AAPL"",,'ThomTrade-charts.xlsb'!Chart_Series_W_Gain_AAPL,1)"
推荐答案
MSDN表示不能使用字母C或R(上/下)作为名称。我认为有一个错误,当这封信是名字中的第一个字母是R或C(或r或c),我复制了你的错误。
MSDN indicates you can't use the letters "C" or "R" (upper/lower) as names. I think there is a bug relating to when this letter is the first letter in the Name is either R or C (or r or c) which I have replicated your error.
尝试使用连接到公式的字符串中的名称地址,如下所示:
Try using the Name's address in a string concatenated to your formula, like so:
Sub ChtSeries()
Dim objChartWGain As Chart
Dim objSeries As Series
Dim nmAddress As String
Dim n As Name
Set objChartWGain = Charts("W Gain")
'Replace with your Name definition:'
Set n = ActiveWorkbook.Names.Add("Chart_Series_W_Gain_AAPL", Sheets("Sheet2").Range("A2:A4"))
'Turn the Name's address in to a usable string:'
nmAddress = Replace(n.RefersTo, "=", vbNullString)
Set objSeries = objChartWGain.SeriesCollection(1)
objSeries.Formula = "=SERIES(""AAPL"",," & nmAddress & ",1)"
End Sub
来自MSDN的信息:
您不能使用大写和小写字符C, c,R,
或r作为定义的名称,因为它们都用作
的缩写,当
输入时,为当前选定的单元格选择行或列他们在名称或转到文本框中。
You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.
这篇关于Excel VBA运行时错误1004仅以“c”开头的名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!