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

在另一个子系统中编辑子系统

excel-vba vba

S31 · 技术社区 · 6 年前

无法从另一个子系统中编辑子系统。我尝试了以下操作:

Sub Sub1()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2")

    ws.Range("A1").Value = "Useless"
End Sub

Sub Sub2()
    Dim test as string
    test = ThisWorkbook.Worksheets("Sheet1").Range("B3").Value

    If test = "here" Then
        Call Sub1 
        With Sub1 
           ws.Range("A2").Value = test.Value
        End With
    End If

expected function or variable 返回错误,可能是因为 Sub1 应该是一个函数。但即使它变成了一个函数, Sub2 代码运行时仍然无法正常工作。

3 回复 | 直到 6 年前

41686d6564 6 年前

实际上,当你试着转变的时候,你是在正确的轨道上 Sub1 一个函数。您只需要使函数“return”成为已创建的工作表对象。在这里:

Function Sub1() As Worksheet     ' Don't forget to use a different name for Sub1 (and
'                                  preferably Sub2 as well).
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet2")
    ws.Range("A1").Value = "Useless"

    Set Sub1 = ws
End Function

Sub Sub2()
    Dim test As String
    test = ThisWorkbook.Worksheets("Sheet1").Range("B3").Value

    If test = "here" Then
        With Sub1
           .Range("A2").Value = test
        End With
    End If
End Sub

第1款

另外,请注意我使用 test 而不是你的 test.Value 测试 已经是字符串,没有 Value

Vityata 6 年前

Public ws As Worksheet

Sub Sub1()

    Set ws = ThisWorkbook.Worksheets("Sheet2")
    ws.Range("A1") = "Useless"

End Sub

Sub Sub2()

    Dim test As String        
    test = ThisWorkbook.Worksheets("Sheet1").Range("B3")
    If test = "here" Then
        Sub1
        ws.Range("A2") = test
    End If

End Sub

Darren Bartrup-Cook 6 年前

另一种方法是将变量作为参数传递:

Sub Sub1(ByRef ws As Worksheet)  'The variable is passed ByRef as default, but wanted to make it clear.

    Set ws = ThisWorkbook.Worksheets("Sheet2")

    ws.Range("A1") = "Useless" 'Value is the default property of Range.

End Sub

Sub Sub2()

    Dim test As String
    Dim wrkSht As Worksheet

    test = ThisWorkbook.Worksheets("Sheet1").Range("B3")

    If test = "here" Then
        Sub1 wrkSht 'Pass the variable to Sub1 to get defined.
        wrkSht.Range("A2") = test
    End If
End Sub

或转身 Sub1 返回对工作表的引用的函数中:

Function Sub1() As Worksheet
    Set Sub1 = ThisWorkbook.Worksheets("Sheet2")
End Function

Sub Sub2()
    Dim test As String
    test = ThisWorkbook.Worksheets("Sheet1").Range("B3")

    If test = "here" Then
        Sub1.Range("A2") = test
    End If
End Sub