使用从另一个子系统建立的变量

Question

我有两个子：'PNL_Monthly_Actuals_Main'&'PNL_Monthly_Actuals1'。"Main"子中使用的fname字符串与第二个子"Actuals1"相关。Main子例程根据输入框中输入的fname打开PNL工作簿，然后选择打开的预算工作簿以执行"PNL_Monthly_Actuals1"子例程。在"…Actuals1"子例程中，我重复输入框以输入fname，这样我就可以将其用于SUMPRODUCT公式并设置工作簿维度。如何避免重复输入框并引用"…Main"子中的fname？

Sub PNL_Monthly_Actuals_Main()
 Dim fpath As String
 Dim fname As String
 fpath = "I:Finance & AccountingFinanceBudget 2015Supporting FilesPNL's"
 **fname = InputBox("Enter PNL File Name")**
 Set PNLWb = Application.Workbooks.Open(fpath & "" & fname & ".xlsx")

Dim wb As Workbook
Dim sheet As Worksheet
Dim YesOrNoAnswerToMessageBox As String
For Each wb In Application.Workbooks
If Left(wb.Name, 6) = "Budget" Then
YesOrNoAnswerToMessageBox = MsgBox("Would you like to run the macro on " & wb.Name & "?", vbYesNo, "Where to run marco?")
If YesOrNoAnswerToMessageBox = vbYes Then
wb.Activate
With wb
For Each sheet In wb.Worksheets
If Left(sheet.Name, 2) = "By" Then
YesOrNoAnswerToMessageBox = MsgBox("Would you like to run the macro on worksheet " & sheet.Name & "?", vbYesNo, "Where to run marco?")
If YesOrNoAnswerToMessageBox = vbYes Then
sheet.Activate
If sheet.Name = "By SubMarket" Then
'Put sub name here
Call PNL_Monthly_Actuals1
Else
Call PNL_Monthly_Actuals2
End If
End If
End If
Next sheet
End With
End If
End If
Next wb
PNLWb.Close
End Sub
Sub PNL_Monthly_Actuals1()
   'Define Budget Template Workbook
    'Define the BySubMarket tab in the Budget Template Workbook
    'Define the PNL workbook that is being evaluated
    'Define the file path where the PNL reports are stored
    'Input box to manually enter file name to open
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Dim BudWkb As Workbook
    Dim Wk2 As Worksheet
    Dim PNLWkb As Workbook
    Dim fpath As String
    Dim fname As String
    Set BudWkb = ActiveWorkbook
    Set Wk2 = ActiveSheet
    fname = InputBox("Enter PNL File Name")
    Set PNLWkb = Workbooks(fname)


    With PNLWkb
        Dim Wk1 As Worksheet
        Set Wk1 = PNLWkb.Sheets("det")
        With Wk1
        Dim FRow As Long
        Dim lRow As Long
        Dim SbMktCol As Long
        Dim ExpCol As Long
        Dim Expense As String
        Expense = InputBox("Enter Expense GL")

    'to locate begining and ending row of data on PNL report
    'Identifies the column where the SubMarket names are located for lookup purposes
    'Defines the expense GL column to lookup based on the inputbox above
    FRow = Wk1.Cells.Find("66990000", LookAt:=xlPart).Offset(2, 0).row
    lRow = Wk1.Cells.Find("66990000", LookAt:=xlPart).End(xlDown).Offset(-1, 0).row
    SbMktCol = Wk1.Cells.Find("Submarket", LookAt:=xlWhole).Column
    ExpCol = Wk1.Cells.Find(Expense, LookAt:=xlPart).Column
    'Defines the Range of the Sub-Market Names
    Dim SbMktRg As Range
    Set SbMktRg = Wk1.Range(Wk1.Cells(FRow, SbMktCol), Wk1.Cells(lRow, SbMktCol))
    'Defines the exact range of the expense column being analyzed
    Dim ExpRg As Range
    Set ExpRg = Wk1.Range(Wk1.Cells(FRow, ExpCol), Wk1.Cells(lRow, ExpCol))
        End With
    End With



    With BudWkb
        With Wk2
            Dim Period As String
            Period = InputBox("Enter MM/D/YYYY of Period for Oct-Dec or Enter M/D/YYYY for periods prior to Oct")
            Dim ActualCol As Long
            ActualCol = Wk2.Cells.Find(Period, LookAt:=xlPart).Offset(0, 1).Column
            Dim sRow As Long
            Dim SubCol As Long
            Dim eRow As Long

            sRow = Wk2.Cells.Find("Sub-Market", LookAt:=xlWhole).Offset(1, 0).row
            SubCol = Wk2.Cells.Find("Sub-Market", LookAt:=xlWhole).Column
            eRow = Wk2.Range(Wk2.Cells(sRow, SubCol), Wk2.Cells(rows.Count, SubCol)).Find("TOTAL", LookAt:=xlWhole).Offset(-1, 0).row
            Dim ActualRg As Range
            Set ActualRg = Wk2.Range(Wk2.Cells(sRow, ActualCol), Wk2.Cells(eRow, ActualCol))

            With ActualRg
                .Formula = "=-SUMPRODUCT(--('[" & fname & ".xlsx]det'!" & SbMktRg.Address & "=C4),'[" & fname & ".xlsx]det'!" & ExpRg.Address & ")"
                .Value = .Value
            End With

            Dim pLRow As Long
            pLRow = Wk2.Cells.Find("P/L Sub-Markets Total", LookAt:=xlWhole).row
            With Wk2.Cells(pLRow, ActualCol)
            .Formula = "=-SUMPRODUCT(--('[" & fname & ".xlsx]det'!" & SbMktRg.Address & "<>""""),'[" & fname & ".xlsx]det'!" & ExpRg.Address & ")"
            End With
        End With
    End With



    'optional:
    'ThisWorkbook.SaveAs Filename:="YYYYMMDD.xls"
    Application.ScreenUpdating = True
End Sub

Answer 1

您需要全局声明变量。示例：

Dim myVar As String '<-- globally declared, out of any specific sub-function
Sub myMacro1()
    myVar = "ciao"
    myMacro2
End Sub
Sub myMacro2()
    MsgBox myVar '<-- this was defined in myMacro1, but it's used in myMacro2
End Sub

在您的具体情况下，只需取出

Dim fname As String

从Sub中，并将其放在它的外部。宏Actuals1会记住这一点。

或者，将其作为参数传递，这意味着重写宏声明：

Sub PNL_Actuals1(ByVal fname As String)
End Sub

然后调用参数为的宏

Call PNL_Actuals1(fname)

甚至更好的

PNL_Actuals1 fname