excel - VBA If/ElseIf-编译错误: Duplication in Current Scope

Question

我刚刚开始学习VBA，并且对以下内容有些困惑。谢谢您的协助。

通过以下操作，我得到了:编译错误:在ElseIf (ActiveSheet.Name) = "BA Tracker" Then行的folderPathWithName As String之后，当前作用域中有重复项。

我的假设是，最初的If中的内容不会影响后续的ElseIf。如果不是这种情况，那么我真的不确定要从ElseIf中拿出什么来使这项工作生效。

谢谢你的帮助。

Sub CopyFile()

    Dim oFSO As Object
    Dim SourceFile As String
    Dim DestinationFolder As String
    Dim startPath As String
    Dim myName As String
    Dim FileYear As String
    Dim FileMonth As String
    Dim AgentName As String
    Dim Agreement As String
    Dim CallDate As String
    Dim wb As Workbook
    Dim ws1112 As Worksheet
    Dim ws2221 As Worksheet
    Dim s As String
    Dim r As String
    Dim cst As String
    Dim cd As String
    Dim ass As String
    Dim ty As String
    Dim an As String
    Dim ss As String
    Dim si As String
    Dim sour As String


    FileYear = Range("A2")
    FileMonth = Range("A3")
    AgentName = Range("D1")
    Agreement = Range("D2")
    CallDate = Range("D3")

If (ActiveSheet.Name) = "Sitel Audit" Then

    startPath = "C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\"
    myName = ActiveSheet.Range("D1").Text  ' Change as required to cell holding the folder title

' check if folder exists, if yes, end, if not, create
   Dim folderPathWithName As String
    folderPathWithName = startPath & Application.PathSeparator & myName

    If Dir(folderPathWithName, vbDirectory) = vbNullString Then
        MkDir folderPathWithName

    End If

Set oFSO = CreateObject("Scripting.FileSystemObject")

SourceFile = "C:\Users\matthew.varnham\Desktop\QA Improvements\Customer service Inbound scorecard v9.xlsm"
DestinationFolder = "C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\" & AgentName & "\"

oFSO.CopyFile Source:=SourceFile, Destination:=DestinationFolder & "\" & AgentName & " - " & Agreement & ".xlsm"

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 12), Address:=("C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm"), TextToDisplay:="OPEN"

Set ws1112 = Sheets("Sitel Audit")
s = ws1112.Range("D1").Value 'Agent Name
r = ws1112.Range("D3").Value 'Call Date
cst = ws1112.Range("D4").Value 'Call Start Time
cd = ws1112.Range("D5").Value 'Call Duration
ass = ws1112.Range("D6").Value 'Assessor Initials
ty = ws1112.Range("D7").Value 'Call Type
an = ws1112.Range("D2").Value 'Agreement Number
ss = ws1112.Range("D8").Value 'Sitel Score
si = ws1112.Range("E1").Value & FileYear & "\" & FileMonth & "\" & AgentName & "\" 'Sitel QA Folder
sour = ws1112.Range("A4").Value 'Sitel as Source


Set wb = Workbooks.Open("C:\Users\matthew.varnham\Desktop\QA Improvements\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm")
Set ws2221 = wb.Sheets("Observation Sheet")

ws2221.Range("B5:C5").Value = s 'Agent Name
ws2221.Range("E5").Value = r 'Call Date
ws2221.Range("F5").Value = cst 'Call Start Time
ws2221.Range("G5").Value = cd 'Call Duration
ws2221.Range("B8:C8").Value = ass 'Assessor Initials
ws2221.Range("B11:C11").Value = ty 'Call Type
ws2221.Range("E8:G8").Value = an 'Agreement Number
ws2221.Range("D4").Value = ss 'Sitel Score
ws2221.Range("G51").Value = si 'Sitel QA Folder
ws2221.Range("C3").Value = sour 'Sitel as Source

ElseIf (ActiveSheet.Name) = "BA Tracker" Then

    startPath = "C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\"
    myName = ActiveSheet.Range("D1").Text  ' Change as required to cell holding the folder title

' check if folder exists, if yes, end, if not, create
   Dim folderPathWithName As String
    folderPathWithName = startPath & Application.PathSeparator & myName

    If Dir(folderPathWithName, vbDirectory) = vbNullString Then
        MkDir folderPathWithName

    End If

Set oFSO = CreateObject("Scripting.FileSystemObject")

SourceFile = "C:\Users\matthew.varnham\Desktop\QA Improvements\Customer service Inbound scorecard v9.xlsm"
DestinationFolder = "C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\" & AgentName & "\"

oFSO.CopyFile Source:=SourceFile, Destination:=DestinationFolder & "\" & AgentName & " - " & Agreement & ".xlsm"

ActiveSheet.Hyperlinks.Add Anchor:=ActiveCell.Offset(0, 13), Address:=("C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm"), TextToDisplay:="OPEN"

Set ws1112 = Sheets("BA Tracker")
s = ws1112.Range("D1").Value 'Agent Name
r = ws1112.Range("D3").Value 'Call Date
cst = ws1112.Range("D4").Value 'Call Start Time
cd = ws1112.Range("D5").Value 'Call Duration
ass = ws1112.Range("D6").Value 'Assessor Initials
ty = ws1112.Range("D7").Value 'Call Type
an = ws1112.Range("D2").Value 'Agreement Number
ss = ws1112.Range("D8").Value 'Sitel Score
si = ws1112.Range("E1").Value & FileYear & "\" & FileMonth & "\" & AgentName & "\" 'Sitel QA Folder
sour = ws1112.Range("A4").Value 'Sitel as Source


Set wb = Workbooks.Open("C:\Users\matthew.varnham\Desktop\QA Improvements\BA Tracker\" & FileYear & "\" & FileMonth & "\" & AgentName & "\" & AgentName & " - " & Agreement & ".xlsm")
Set ws2221 = wb.Sheets("Observation Sheet")

ws2221.Range("B5:C5").Value = s 'Agent Name
ws2221.Range("E5").Value = r 'Call Date
ws2221.Range("F5").Value = cst 'Call Start Time
ws2221.Range("G5").Value = cd 'Call Duration
ws2221.Range("B8:C8").Value = ass 'Assessor Initials
ws2221.Range("B11:C11").Value = ty 'Call Type
ws2221.Range("E8:G8").Value = an 'Agreement Number
ws2221.Range("D4").Value = ss 'Sitel Score
ws2221.Range("G51").Value = si 'Sitel QA Folder
ws2221.Range("C3").Value = sour 'Sitel as Source


End If

Workbooks("SITEL - Inbound Tracker.XLSM").Close SaveChanges:=True

End Sub

Answer 1

您要声明两次folderPathWithName变量-一次在If块中，然后在'ElseIf`块中。

只需从ElseIf块内删除Dim folderPathWithName As String行，并将Dim folderPathWithName As String块内的If行与所有其他变量声明一起移动即可。

我建议您始终在过程开始时声明所有变量，而不是在您认为需要它们时声明它们。这样可以阻止这种情况的发生，并使代码保持整洁。

问候，