代码之家 › 专栏 › 技术社区 › Robillard

不能在同一模块的其他子模块中使用输入框中的变量

inputbox vba excel

Robillard · 技术社区 · 7 年前

所以我试着从输入框中获取一个变量。然后我用这个变量来命名我的工作表。但是我不能在其他Sub中引用该表以切换回它。这给了我一个错误。我的代码可能是草率的,因为我是新的,我分离我的sub可能比我需要的多,但我一直在测试他们一块一块的时间。无论如何,它在这里:

Dim name As String
name = InputBox("Please enter the date of the report. Ex: 7-28 to 8-25-17. This will show up as: All HCM changes 7-28 to 8-25-17 for the tab name.", "Tab Name Date")
If Len(name) = 0 Then 'Checking if Length of name is 0 characters
    MsgBox "Valid date not entered. Please Re-Run the Macro to input the date.", vbCritical
Else
    MsgBox "The tab will now be named, All HCM changes " & name & "."
End If

Sheets("Sheet1").Select
Sheets("Sheet1").name = ("All HCM changes " & name)

Call Change_Header_Colors
Call Insert_Columns
Call Create_LEGEND
Call Sort_by_Action_then_Last_Name
Call Freeze_Panes

在这些调用中,我引用了name变量。但这给了我一个错误。我尝试在模块中的第一个子元素之前声明变量,但没有成功。这就是我在这些电话中使用它的方式。

Sub Sort_by_Action_then_Last_Name()
'
' This is ACTUALLY sorting by action then by person #. Which is what we 
wanted. Can change it easily.
'

'
Dim name As String

ActiveWorkbook.Worksheets("All HCM changes " & name).Select
Cells.Select
ActiveWorkbook.Worksheets("All HCM changes " & name).SORT.SortFields. _
    Clear
ActiveWorkbook.Worksheets("All HCM changes " & name).SORT.SortFields. _
    Add Key:=Range("A2:A246"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("All HCM changes " & name).SORT.SortFields. _
    Add Key:=Range("E2:E246"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("All HCM changes " & name).SORT
    .SetRange Range("A1:U246")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

2 回复 | 直到 7 年前

Scott Craner 7 年前

您需要将变量传递给其他子节点:

Sub Sort_by_Action_then_Last_Name(name as String)

Dim name as String 当您在调用中声明变量时,从辅助子对象的行。

然后当你叫它的时候,你会这样叫它:

Call Sort_by_Action_then_Last_Name(name)

tk78 7 年前

Sub Main()

    Dim name As String

    name = InputBox("Please enter worksheet name")

    'call your other procedures
    Change_Header_Color name

End Sub

Sub Change_Header_Color(ByVal name As String)

    'do some stuff here

    'use the input from the parameter in some way
    Worksheets("foo").Range("A1").Value = name

End Sub

Private name As String

Sub Main()

    name = InputBox("Please enter worksheet name")

    'call your other procedures
    Change_Header_Color

End Sub

Sub Change_Header_Color()

    'do some stuff here

    'use the input from the parameter in some way
    Worksheets("foo").Range("A1").Value = name

End Sub

推荐文章