为工作簿中的每个工作表调用函数VBA

Question

我是VBA的初学者，似乎无法弄清楚如何在工作簿中的所有工作表中调用函数。有没有办法调用这样的函数？如果可能的话，我希望它在2个独立的Subs中。谢谢。

Sub Formuoli2() 

Dim iLastRow As Integer 
Dim i As Integer 
Dim ws As Worksheet 
    iLastRow = 5 
    For i = 1 To iLastRow 
     Range("A" & i) = "a" 'these are formulas 
     Range("B" & i) = "b" 'these are formulas 
     Range("C" & i) = "c" 'these are formulas 
     Range("D" & i) = "d" 'these are formulas 
     Range("E" & i) = "e" 'these are formulas 
     Range("F" & i) = "f" 
    Next i 
End Sub 

Sub Formuoli3() 
Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Worksheets 
     With ws 
      Call Formuoli2 
     End With 
    Next 
End Sub 

Answer 1

添加一行：

.Select 
Call Formuoli2 

Answer 2

您可以直接通过工作表作为参数传递给第二子：

Sub Formuoli2(ws As Worksheet) 

    Dim iLastRow    As Long 
    Dim i      As Long 

    iLastRow = 5 
    For i = 1 To iLastRow 
     ws.Range("A" & i) = "a" 'these are formulas 
     ws.Range("B" & i) = "b" 'these are formulas 
     ws.Range("C" & i) = "c" 'these are formulas 
     ws.Range("D" & i) = "d" 'these are formulas 
     ws.Range("E" & i) = "e" 'these are formulas 
     ws.Range("F" & i) = "f" 
    Next i 
End Sub 

Sub Formuoli3() 
    Dim ws     As Worksheet 
    For Each ws In ThisWorkbook.Worksheets 
     Call Formuoli2(ws) 
    Next 
End Sub 

Answer 3

这是更好地传递ws到Formuoli2()子

Option Explicit 

Sub Formuoli3() 
    Dim ws As Worksheet 
    For Each ws In ThisWorkbook.Worksheets 
     Formuoli2 ws 
    Next 
End Sub 


Sub Formuoli2(ws As Worksheet) 
    Dim iLastRow As Integer 
    Dim i As Integer 

    iLastRow = 5 
    With ws 
     For i = 1 To iLastRow 
      .Range("A" & i).Resize(, 6) = Array("a", "b", "c", "d", "e", "f") 'these are formulas 
     Next i 
    End With 
End Sub