选择错误中每个单元格的 Excel VBA 函数导致变量未定义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28582557/
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
Excel VBA Function For Each Cell in Selection error resulting to Variable Not Defined
提问by Kay Singian
I was writing A code function in excel that calls a subroutine after the function gets the input data. The function is to color a cell or range of cells within a worksheet or another worksheet using the RGB values in the excel.
我在excel中编写了一个代码函数，在函数获取输入数据后调用子程序。该功能是使用 Excel 中的 RGB 值为工作表或其他工作表中的单元格或单元格区域着色。
However, I do not know how to call the cell values with the function I wrote once the subroutine is working. The Error says Variable not Defined. Looks like the "For Each Cell in Selection" line is causing the error, as the cell is not declared once I passed the data of the function to the subroutine. Also, can someone provide the code on modifying other worksheet's cells in the form of: "Cell.Interior.Color = RGB(Red, Green, Blue)"? The worksheet's name is provided to be input by the user.
但是，一旦子例程运行，我不知道如何使用我编写的函数调用单元格值。错误提示未定义变量。看起来“For Each Cell in Selection”行导致了错误，因为一旦我将函数的数据传递给子例程，就不会声明该单元格。另外，有人可以提供以下形式的修改其他工作表单元格的代码：“Cell.Interior.Color = RGB(Red, Green, Blue)”？提供工作表的名称以供用户输入。
Option Explicit
Dim Activate As Boolean
Public Function SETRGBCOLOR( _
Optional CurrentCellValue As Variant, _
Optional wksSheetname As String, _
Optional rngRange As Range, _
Optional byteRed As Byte = 0, _
Optional byteGreen As Byte = 0, _
Optional byteBlue As Byte = 0) As Variant
If IsMissing(CurrentCellValue) Then
SETRGBCOLOR = ""
Else
SETRGBCOLOR = CurrentCellValue
End If
Activate = False
'set greater RGB values than max RG value to 255, byte ignores the negative value of a digit
If byteRed > 255 Then byteRed = 255
If byteGreen > 255 Then byteGreen = 255
If byteBlue > 255 Then byteBlue = 255
Activate = True
Call CalculateColor(wksSheetname, rngRange, byteRed, byteGreen, byteBlue)
End Function
Private Sub CalculateColor(wksSheetname As String, rngRange As Range, byteRed As Byte, byteGreen As Byte, byteBlue As Byte)
If Activate = True Then
If Trim(wksSheetname) <> "" Then
If Trim(rngRange) <> "" Then
For Each Cell In Selection
Cell.Interior.Color = RGB(byteRed, byteGreen, byteBlue)
Next Cell
Else
End If
Else
If Trim(rngRange) <> "" Then
'For Each Cell In Selection
' Cell.Interior.Color = RGB(byteRed, byteGreen, byteBlue)
'Next Cell
Else
End If
End If
End If
Activate = False
End Sub
回答by grahamj42
You have Option Explicitso the variable Cellmust be declared :
Dim Cell As Rangeat the start of your Sub CalculateColor().
您必须声明Option Explicit变量Cell：
Dim Cell As Range在您的 SubCalculateColor() 的开头。
