VBA，错误 400，在电子表格之间切换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11098508/
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
VBA, Error 400, switching between spreadsheets
提问by Eva
I call from a spreadsheet module a function that does some processing in another sheet and returns an integer. I then want to write that integer in a cell in the sheet from where the function has been called. I receive an error 400, I guess I do not correctly handle the switching between the sheets.
我从电子表格模块调用一个函数，该函数在另一个工作表中进行一些处理并返回一个整数。然后我想在调用函数的工作表中的单元格中写入该整数。我收到错误 400，我想我没有正确处理工作表之间的切换。
Could you help me with that?
你能帮我解决这个问题吗？
Function lastNonZero(Rng As Range) As Integer
i = 19
Do While ActiveCell.Value = 0
ActiveCell.Offset(0, -1).Activate
i = i - 1
Loop
lastNonZero = i
End Function
Extract from the Sub, located in Sheet A's module:
从位于 Sheet A 模块中的 Sub 中摘录：
For j = startRow To startRow + (nRows - 1)
Worksheets("B").Select
Range("Y" & j).Activate
k = lastNonZero(Worksheets("B").Range("Y" & j))
Worksheets("A").Range("BZ" & j) = k
Next j
回答by assylias
Try to replace the Range("Y" & j)line with:
尝试用以下内容替换该Range("Y" & j)行：
Worksheets("B").Range("Y" & j).Activate
When you call Range(xxx)in a Sheet's module, it refers to a range in that sheet, whether it is selected or not. But if the sheet is not selected and you try to select/activate the range, it will return an error because you can't select a range on a sheet that is not the active sheet.
当您调用Range(xxx)工作表的模块时，它会引用该工作表中的一个范围，无论它是否被选中。但是，如果未选择工作表而您尝试选择/激活范围，它将返回错误，因为您无法在非活动工作表的工作表上选择范围。
ps: that does not change the fact that on the next line (k = lastNonZero(Worksheets("B").Range("Y" & j))), the argument is not used in the lastNonZerofunction).
ps：这不会改变下一行 ( k = lastNonZero(Worksheets("B").Range("Y" & j))) 中未在lastNonZero函数中使用参数的事实)。
