在 VBA 中，我如何解决错误："Duplicate Declaration in Current Scope" 如果我希望我的代码根据复选框执行某些任务(if/else)

Question

这里我只想要它，这样如果单击另一个复选框，它就会在Else块中运行代码。代码运行得很好，只有If Choice1是True块，但当我添加Else块时，我会得到一个Duplicate Declaration in Current Scope错误：

Private Sub CommandButton1_Click()
If Choice1.Value = True Then

Dim filename As String
filename = ThisWorkbook.Path & Application.PathSeparator & "somefile.xlsm"

Dim wk As Workbook
Set wk = Workbooks.Open(filename, ReadOnly:=True)

Dim rgSource As Range, rgDestination As Range

' set rgsource = [workbook].[worksheet].[range]
Set rgSource = ThisWorkbook**strong text**.Worksheets("Source").**Range("A1:B7")**
Set rgDestination = ThisWorkbook.Worksheets("Destination").Range("A1")

rgSource.Copy
rgDestination.PasteSpecial xlPasteValues

wk.Close saveChanges:=False

ElseIf Choice2.Value = True Then
Dim filename As String
filename = ThisWorkbook.Path & Application.PathSeparator & "Otherfile.xlsm"
Dim wk As Workbook
Set wk = Workbooks.Open(filename, ReadOnly:=True)
Dim rgSource As Range, rgDestination As Range
' set rgsource = [workbook].[worksheet].[range]
Set rgSource = wk.Worksheets("Sheet1").**Range("E1:F7")**
Set rgDestination = ThisWorkbook.Worksheets("Destination").Range("A1")
rgSource.Copy
rgDestination.PasteSpecial xlPasteValues
wk.Close saveChanges:=False
ElseIf Choice3.Value = True Then ...etc.

End If
End Sub

Answer 1

在代码开始时声明所有变量。您可以在任何地方声明，但最好先声明变量。尝试低于子

Private Sub CommandButton1_Click()
Dim filename As String
Dim wk As Workbook
Dim rgSource As Range, rgDestination As Range

If Choice1.Value = True Then
filename = ThisWorkbook.Path & Application.PathSeparator & "somefile.xlsm"
Set wk = Workbooks.Open(filename, ReadOnly:=True)
' set rgsource = [workbook].[worksheet].[range]
Set rgSource = ThisWorkboo.Worksheets("Source").Range("A1:B7")
Set rgDestination = ThisWorkbook.Worksheets("Destination").Range("A1")
rgSource.Copy
rgDestination.PasteSpecial xlPasteValues
wk.Close saveChanges:=False
ElseIf Choice2.Value = True Then
filename = ThisWorkbook.Path & Application.PathSeparator & "Otherfile.xlsm"
Set wk = Workbooks.Open(filename, ReadOnly:=True)
' set rgsource = [workbook].[worksheet].[range]
Set rgSource = wk.Worksheets("Sheet1").Range("E1:F7")
Set rgDestination = ThisWorkbook.Worksheets("Destination").Range("A1")
rgSource.Copy
rgDestination.PasteSpecial xlPasteValues
wk.Close saveChanges:=False
ElseIf Choice3.Value = True Then
DoEvents
End If
End Sub

Answer 2

如果不使用Option Explicit，则可以安全地省略声明并解决问题：(

但这不是一个好的做法，只是其他答案的替代方案。

Answer 3

您只需要声明一次变量，就可以像这样将其放置在上面：

Private Sub CommandButton1_Click()

Dim filename As String
Dim wk As Workbook
Dim rgSource As Range, rgDestination As Range

If Choice1.Value = True Then

filename = ThisWorkbook.Path & Application.PathSeparator & "somefile.xlsm"

Set wk = Workbooks.Open(filename, ReadOnly:=True)

' set rgsource = [workbook].[worksheet].[range]
Set rgSource = ThisWorkbook.Worksheets("Source").Range("A1:B7")
Set rgDestination = ThisWorkbook.Worksheets("Destination").Range("A1")

rgSource.Copy
rgDestination.PasteSpecial xlPasteValues

wk.Close saveChanges:=False

ElseIf Choice2.Value = True Then

filename = ThisWorkbook.Path & Application.PathSeparator & "Otherfile.xlsm"

Set wk = Workbooks.Open(filename, ReadOnly:=True)
' set rgsource = [workbook].[worksheet].[range]
Set rgSource = wk.Worksheets("Sheet1").Range("E1:F7")
Set rgDestination = ThisWorkbook.Worksheets("Destination").Range("A1")
rgSource.Copy
rgDestination.PasteSpecial xlPasteValues
wk.Close saveChanges:=False
ElseIf Choice3.Value = True Then ...etc.

End If
End Sub