excel vba如何逐语句运行代码ExcelVBA调用有参数过程

导读直接看代码和注释：OptionExplicitSubSortSheets()'Thisroutinesortsthesheetsofthe'activewo....

直接看代码和注释：Option ExplicitSub SortSheets() This routine sorts the sheets of the active workbook in ascending order. Use Ctrl Shift S to execute Dim SheetNames() As String Dim i As Long Dim SheetCount As Long Dim OldActive As Object If ActiveWorkbook Is Nothing Then Exit Sub No active workbook SheetCount = ActiveWorkbook.Sheets.Count Check for protected workbook structure If ActiveWorkbook.ProtectStructure Then MsgBox ActiveWorkbook.Name & " is protected.", _ vbCritical, "Cannot Sort Sheets." Exit Sub End If Make user verify If MsgBox("Sort the sheets in the active workbook?", _ vbQuestion vbYesNo) <> vbYes Then Exit Sub Disable Ctrl Break Application.EnableCancelKey = xlDisabled Get the number of sheets SheetCount = ActiveWorkbook.Sheets.Count Redimension the array ReDim SheetNames(1 To SheetCount) Store a reference to the active sheet Set OldActive = ActiveSheet Fill array with sheet names For i = 1 To SheetCount SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next i Sort the array in ascending order Call BubbleSort(SheetNames) Turn off screen updating Application.ScreenUpdating = False Move the sheets For i = 1 To SheetCount ActiveWorkbook.Sheets(SheetNames(i)).Move _ Before:=ActiveWorkbook.Sheets(i) Next i Reactivate the original active sheet OldActive.Activate End SubSub BubbleSort(List() As String) Dim First As Long, Last As Long Dim i As Long, j As Long Dim Temp As String First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i 1 To Last If UCase(List(i)) > UCase(List(j)) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next iEnd Sub，今天小编就来说说关于excel vba如何逐语句运行代码?下面更多详细答案一起来看看吧!

excel vba如何逐语句运行代码

直接看代码和注释：

Option ExplicitSub SortSheets() This routine sorts the sheets of the active workbook in ascending order. Use Ctrl Shift S to execute Dim SheetNames() As String Dim i As Long Dim SheetCount As Long Dim OldActive As Object If ActiveWorkbook Is Nothing Then Exit Sub No active workbook SheetCount = ActiveWorkbook.Sheets.Count Check for protected workbook structure If ActiveWorkbook.ProtectStructure Then MsgBox ActiveWorkbook.Name & " is protected.", _ vbCritical, "Cannot Sort Sheets." Exit Sub End If Make user verify If MsgBox("Sort the sheets in the active workbook?", _ vbQuestion vbYesNo) <> vbYes Then Exit Sub Disable Ctrl Break Application.EnableCancelKey = xlDisabled Get the number of sheets SheetCount = ActiveWorkbook.Sheets.Count Redimension the array ReDim SheetNames(1 To SheetCount) Store a reference to the active sheet Set OldActive = ActiveSheet Fill array with sheet names For i = 1 To SheetCount SheetNames(i) = ActiveWorkbook.Sheets(i).Name Next i Sort the array in ascending order Call BubbleSort(SheetNames) Turn off screen updating Application.ScreenUpdating = False Move the sheets For i = 1 To SheetCount ActiveWorkbook.Sheets(SheetNames(i)).Move _ Before:=ActiveWorkbook.Sheets(i) Next i Reactivate the original active sheet OldActive.Activate End SubSub BubbleSort(List() As String) Dim First As Long, Last As Long Dim i As Long, j As Long Dim Temp As String First = LBound(List) Last = UBound(List) For i = First To Last - 1 For j = i 1 To Last If UCase(List(i)) > UCase(List(j)) Then Temp = List(j) List(j) = List(i) List(i) = Temp End If Next j Next iEnd Sub

也可用select选择调用过程：

Sub main() Dim SubToCall As String Select Case Weekday(Now) Case 1, 7: SubToCall = "WeekEnd" Case Else: SubToCall = "Daily" End Select Application.Run SubToCallEnd SubSub WeekEnd() MsgBox "Today is a weekend"End SubSub Daily() MsgBox "Today is not a weekend"End Sub

类似的，当然也可以建立一个字符串数组（元素为过程名）来循环调用。

reference: www.wiley.com/go/excel2016powerprogramming

-End-