VBA：将值存储到变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16792821/
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: Store value to variable
提问by HerrWalter
Sub test() ' ' test Macro '
' Dim aRange As Range Dim i As Integer
Set aRange = Range("A1:A255") Range("A1").Select For i = 1 To aRange.Count - 1 If InStr(ActiveCell.Value, "Last name") Then Call CopyContents End If ActiveCell.Offset(1, 0).Select Next iEnd Sub
Sub CopyContents() Dim currentRange As Range Dim genderAndDiscipline As String
Set currentRange = Range(ActiveCell.Address) 'get the gender and dicipline Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value 'genderAndDiscipline = genderAndDiscipline.Split(" ")End Sub
子测试（）''测试宏'
' Dim aRange As Range Dim i As Integer
Set aRange = Range("A1:A255") Range("A1").Select For i = 1 To aRange.Count - 1 If InStr(ActiveCell.Value, "Last name") Then Call CopyContents End If ActiveCell.Offset(1, 0).Select Next i结束子
Sub CopyContents() 将 currentRange 调暗为范围将性别和纪律调暗为字符串
Set currentRange = Range(ActiveCell.Address) 'get the gender and dicipline Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value 'genderAndDiscipline = genderAndDiscipline.Split(" ")结束子
Hi There, I'm trying to store a cell value in a variable. But somehow it's keep giving mee an compile error. "Object required"
您好，我正在尝试将单元格值存储在变量中。但不知何故，它一直给我一个编译错误。“所需对象”
In my opinion I'm telling the variable to aspect a string and the cell is containing a string, as the debugger says.
在我看来，正如调试器所说，我告诉变量对字符串进行切面，并且单元格包含一个字符串。
Could you help me out?
你能帮我吗？
The currentRange is 'A7' here and the cell above is containing a string with '200m men'
currentRange 在这里是“A7”，上面的单元格包含一个带有“200m men”的字符串
The error occures at
Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
错误发生在
Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
回答by Daniel
genderAndDisciplineis declared as a string.
genderAndDiscipline声明为字符串。
The correct way to assign to a string is using Letinstead of Set(which is used for assigning objects).
分配给字符串的正确方法是使用Let代替Set（用于分配对象）。
In order to get rid of the error, remove the word Setfrom the line causing the error, or replace Setwith Let.
为了摆脱错误，Set从导致错误的行中删除单词，或替换Set为Let。
That is, use one of the following two alternatives (which are equivalent):
也就是说，使用以下两种替代方法之一（等效）：
genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
or
或者
Let genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
回答by Gaffi
The Setkeyword works with Objects. Since you are looking just to save the value, you should leave this out. i.e. Change:
该Set关键字的工作有Objects。由于您只是想保存值，因此您应该忽略它。即更改：
Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
to:
到：
genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
回答by StoriKnow
Remove Setfrom Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
删除Set从Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
