Excel VBA“下标超出范围”运行错误'9'

I have tried to implement the following code so that I can read a cell value and then places this value into another cell on the same Worksheet.

我试图实现以下代码,以便我可以读取单元格值,然后将此值放在同一工作表上的另一个单元格中。

The worksheet has the following name: TestUserGuidance (Has no spaces) The code is as follows:

该工作表具有以下名称:TestUserGuidance(没有空格)代码如下:

Sub GuideTest()
    Dim dblPower, dblMass, dblRatedSpeed, dblRefLength, dblAwot, dblEngineSpeed, dblRoadSpeed As Double
    Dim dblPMR As Double

    dblPower = Worksheets("TestUserGuidance").Cell("B1").Value
    Worksheets("TestUserGuidance").Cell("E1") = dblPower
End Sub

Could anyone please advise where I'm going wrong?

谁能告诉我哪里出错了?

Thanks

3 个解决方案

#1

I think vba can't find the TestUserGuidance sheet. If I run the following in the immediate window I get an "Object doesn't support this property or method" error:

我认为vba找不到TestUserGuidance表。如果我在立即窗口中运行以下命令,我得到一个“对象不支持此属性或方法”错误:

?worksheets(1).cell("A1")

If I run this, I get the error you mention "Subscript out of range":

如果我运行这个,我会收到你提到的“下标超出范围”的错误:

?worksheets("non-existent sheet").cell("A1")

This suggests to me that the active workbook is incorrect since you would have gotten the other error if vba was able to find the worksheet. Can you try adding ThisWorkbook (and also use range)?

这告诉我,活动工作簿是不正确的,因为如果vba能够找到工作表,你会得到另一个错误。你能尝试添加ThisWorkbook(还有使用范围)吗?

dblPower = ThisWorkbook.Worksheets("TestUserGuidance").Range("B1").Value
ThisWorkbook.Worksheets("TestUserGuidance").Range("E1") = dblPower

Another option would be to rename your worksheet:

另一种选择是重命名工作表:

Then you won't have to worry about the active workbook (and also get intellisence):

然后,您将不必担心活动工作簿(并且还得到intellisence):

shtTestUserGuidance.Range("E1") = shtTestUserGuidance.Range("B1")

Also, I would suggest you make all the corrections suggested by others. You will likely have other issues moving forward otherwise.

另外,我建议你做出别人建议的所有更正。否则你可能会遇到其他问题。

Lastly, if the worksheet is like a template and the structure won't change, you'd be better off just using a formula (i.e. E1's formula: =B1). Even if there's conditional logic, it might be easier to maintain as a formula rather than vba code.

最后,如果工作表就像一个模板而且结构不会改变,那么你最好只使用一个公式(即E1的公式:= B1)。即使存在条件逻辑,也可能更容易维护为公式而不是vba代码。

Here are some troubleshooting steps:

以下是一些故障排除步骤:

Toggle a break point on the line where dblPower is set by clicking on the grey area to the left:

通过单击左侧的灰色区域,在设置dblPower的行上切换断点:

Also open the Immediate window if it's not already there by pressing ctrl+g:

如果还没有按下ctrl + g,也打开立即窗口:

When you run the code, it should pause at the break point. Now you can inspect what's in scope. Try typing the following into the Immediate window and press enter:

运行代码时,它应该在断点处暂停。现在您可以检查范围内的内容。尝试在立即窗口中键入以下内容并按Enter键:

?Worksheets("TestUserGuidance").Name

?ActiveWorkbook.Name

Note that you can also step through the code line by line by pressing F8 while the cursor is somewhere within the subroutine. Bottom line is that you may need to examine the code line by line in order to figure this out.

请注意,当光标位于子例程中的某个位置时,您也可以通过按F8逐行执行代码。最重要的是,您可能需要逐行检查代码才能弄清楚这一点。

#2

This is the way to make your code working:

这是使代码工作的方法:

Sub GuideTest()
    Dim dblPower, dblEngineSpeed, dblRoadSpeed As Double ' first two are of variant type
    Dim dblPMR As Double

    dblPower = Worksheets(1).Range("B1").Value
    Worksheets(1).Range("E1") = dblPower
End Sub

When you use "E1" or "B1" you should use Range, not Cells. In my answer Worksheets(1) refers to Worksheets("TestUserGuidance").

当您使用“E1”或“B1”时,您应该使用Range,而不是Cell。在我的回答中,Worksheets(1)引用了工作表(“TestUserGuidance”)。

#3

Only dblRoadSpeed is declared as double. To fix it, declare all your variables as individual, in vba you can do in 2 ways.

只有dblRoadSpeed声明为double。要解决这个问题,请将所有变量声明为个体,在vba中可以通过两种方式进行。

Dim dblPower As Double, dblMass As Double

Dim dblPower As Double
Dim dblMass As Double

Also, you don't need to declare any variable to do what you are doing. You can copy the value in one step.

此外,您不需要声明任何变量来执行您正在执行的操作。您可以一步复制该值。

Worksheets("TestUserGuidance").Range("E1").Value = Worksheets("TestUserGuidance").Range("B1").Value

#1