从另一个Sub调用Sub [英] Calling a Sub from another Sub

问题描述

我正在使用以下宏，但我无法使其正常工作。我试图让第一个子调用第二个子，我需要转移站点值。我将如何正确调用第二个子站点以便转移站点的值。如果我将第二个子实现到第一个子中，这个宏可以正常工作，但我需要知道如何调用它。我使用的是Excel 2003。

'这是第一个子

私人子cbOK_Click（）

< p align = left>卸载我

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim site
Dim quote As Workbook
Dim sum
Dim current As Long
Dim last As Long

site = Application.InputBox（prompt：=" ;请输入站点编号"，标题：="站点"，类型：= 1）
last = Cells.SpecialCells（xlCellTypeLastCell）.Row

如果cbxServiceProviderandCategory =" NEP"那么

























：= msoSortByLastModified，SortOrder：= msoSortOrderAscending）> 0然后
呼叫总计（站点）
结束如果结束

结束如果

Range（" L2"）。Activate
ActiveCell.FormulaR1C1 =" = SUM（RC [-4]：R [1000] C [-4]）"
Range（" L2" ;）。选择
sum = ActiveCell.Value
MsgBox"自2008年以来该网站的总金额为$" &安培;总和& "。"

End Sub

'这是第二个子

子总计（）

For i = 1 To BritainFiles.count
Set quote = Workbooks.Open（.Files（i））
quote.Activate
current = 12
Do while current< = last
If Cells（current，2）= site Then
Cells（current，1）.EntireRow.Copy
Workbooks（" VBA"）。激活
Sheets（" Sheet3"）。激活
范围（"A2"）。选择
Do Until IsEmpty（ActiveCell）
ActiveCell.Offset（1,0）。选择
循环
ActiveSheet.Paste
quote.Activate
current = current + 1
Else
current = current + 1
End If
Loop
quote.Close
Next i

End Sub

解决方案

您的第一个Sub将您的'site'变量传递给第二个Sub ...

< p align = left>

Code Snippet

I am using the following macro, but I can not get it to work properly.  I am trying to get the first sub to call the second sub and I need the site value to be transferred over.  How would I go about calling the second sub correctly so that the value for site is transferred over.  This macro functions properly if I implement the second sub into the first sub, but I need to know how to call it.  I am using Excel 2003.

 

 

'This is the first sub

 

Private Sub cbOK_Click()

Unload Me

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim site
Dim quote As Workbook
Dim sum
Dim current As Long
Dim last As Long

site = Application.InputBox(prompt:="Please Enter the Site Number", Title:="Site", Type:=1)
last = Cells.SpecialCells(xlCellTypeLastCell).Row

 

If cbxServiceProviderandCategory = "NEP" Then
With Application.FileSearch
.NewSearch
.LookIn = "C:\VBA Test"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute(SortBy:=msoSortByLastModified, SortOrder:=msoSortOrderAscending) > 0 Then
Call Total(site)
End If
End With
End If

 

Range("L2").Activate
ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:R[1000]C[-4])"
Range("L2").Select
sum = ActiveCell.Value
MsgBox "The total amount for this site since 2008 is $" & sum & "."

End Sub

 

'This is the second sub

 

Sub Total()

For i = 1 To .FoundFiles.count
Set quote = Workbooks.Open(.FoundFiles(i))
quote.Activate
current = 12
Do While current <= last
If Cells(current, 2) = site Then
Cells(current, 1).EntireRow.Copy
Workbooks("VBA").Activate
Sheets("Sheet3").Activate
Range("A2").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Paste
quote.Activate
current = current + 1
Else
current = current + 1
End If
Loop
quote.Close
Next i

End Sub

解决方案

 

Your first Sub is passing your 'site' variable to the second Sub...

 

Code Snippet

这篇关于从另一个Sub调用Sub的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！