vba 如何在变量中存储一系列单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22690084/
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
How do I store a range of cells in a variable
提问by user2920648
I'm currently working on a Sudoku program as a project in excel using VBA and I need to store a Range of cells in a variable.
我目前正在使用 VBA 将数独程序作为 Excel 中的一个项目，我需要在变量中存储一系列单元格。
Something along the lines of this:
类似的东西：
'''
dim grid as string
grid = range("b3:j11")
'''
But I'm not sure what to dim grid as. I've tried integer, string and range but grid never seems to have a value. I'm also assuming I'm not assigning the range to the variable correctly
但我不确定将网格调暗为什么。我试过整数、字符串和范围，但网格似乎从来没有值。我还假设我没有正确地将范围分配给变量
/////////////////////////////////
///////////////////////////////////
Following the replies im pasting the code here. The duplicatecheck function looks through a range of cells to check if their are more than one of a certain number and then highlight the cells accordingly
根据回复，我将代码粘贴到此处。重复检查功能查看一系列单元格以检查它们是否超过一定数量的一个，然后相应地突出显示单元格
As i want to look through a different range of cells for different checks i need to make the range a variable so the function can be reused
因为我想查看不同范围的单元格进行不同的检查，所以我需要将该范围设为变量，以便可以重用该函数
however when the variable grid is called in the duplicatecheck function it has no value and theirfor no cells are being checked
但是，当在重复检查函数中调用变量 grid 时，它没有值，并且没有检查它们的单元格
Dim row As Integer
Dim col As Integer
Dim grid As Variant
Dim checkmark As Integer
Sub Rowcheck()
checkmark = 1
row = 0
col = 0
grid = range("b3:j3").Value
For row = 0 To 8
duplicatecheck
Next row
End Sub
结束子
Function duplicatecheck()
Dim safe As Boolean
Dim check As Integer
Dim x As Integer
' check each number in a range for duplicates starting with 1
For check = 1 To 9
If Application.Worksheet.Function.CountIf(Selection.Offset(row, col), range(grid)).check = 1 Then
' if number is only found once
safe = True
ElseIf Application.Worksheet.Function.CountIf(Selection.Offset(row, col), range(grid)).check < 1 Then
' if the number is found less than once in the range
safe = False
ElseIf Application.Worksheet.Function.CountIf(Selection.Offset(row, col), range(grid)).check > 1 Then
' if the number is found more than once
Selection.Offset(row, x).range(grid).Interior.colour = vbBlue ' highlight the range red
If checkmark = 1 Then
For x = 0 To 8
Selection.Offset(row, x).range(geid).Value = check
Selection.Offset(row, x).range.ont.colour = vbRed
Next x
ElseIf checkmark = 2 Then
For x = 0 To 8
Selection.Offset(x, col).range(grid).Value = check
Selection.Offset(x, col).range.ont.colour = vbRed
Next x
safe = False
error = True
If safe = False Then
complete = False
End If
End If End If Next check End Function
End If End If Next 检查 End 函数
回答by Ron Rosenfeld
Store it in a variant
将其存储在变体中
Dim vGrid as Variant
vGrid = range("B3:J11")
vGrid will then be a 2D 1-based array, with Dim 1 = rows and Dim2 = columns
vGrid 将是一个基于 1 的二维数组，其中 Dim 1 = 行和 Dim2 = 列
eg the contents of B3 would be in vGrid(1,1)
例如 B3 的内容将在 vGrid(1,1) 中
Edit:It seems you are now using Grid as a string argument to the Range object and having problems with your CountIF.
编辑：看来您现在正在使用 Grid 作为 Range 对象的字符串参数，并且您的 CountIF 出现问题。
Set Option Explicitand the option to require variable declaration as I mentioned in a comment.
设置Option Explicit和我在评论中提到的需要变量声明的选项。
So far as your CountIF statement, if the range you are checking is that declared in Grid, and your criteria is in Selection.Offset(row,col), then the line should look something like:
就您的 CountIF 语句而言，如果您要检查的范围是在 Grid 中声明的范围，并且您的标准在 Selection.Offset(row,col) 中，那么该行应如下所示：
If Application.WorksheetFunction.CountIf(Range(grid),Selection.Offset(Row, col)) = 1 Then ...
Note that I have corrected your using Function as a property of the Worksheet object; put the range and criteria arguments in the proper order; and removed the .check you had added at the end of that function.
请注意，我已更正您使用 Function 作为 Worksheet 对象的属性；以正确的顺序放置范围和条件参数；并删除了您在该函数末尾添加的 .check 。
