30个有用的Excel VBA代码(26~30)

26.一次保存并关闭所有工作簿

如果有许多工作簿打开，并且要保存和关闭这些工作簿，则需要手动转到并保存每个工作簿，然后关闭它。

这是一个VBA代码，它将关闭所有工作簿并在关闭时保存它。

Sub CloseAllWorkbooks() Dim wb As Workbook For Each wb In Workbooks wb.Close SaveChanges:=True Next wb End Sub

请注意，代码只适用于那些先前已经保存过的工作簿。如果有新工作簿，则必须指定要保存该工作簿的文件夹的名称和位置。

27.限制光标在特定区域的移动

如果要限制工作表中的滚动区域，可以使用以下代码执行此操作：

Private Sub Worksheet_Open() Sheets(“Sheet1”).ScrollArea = “A1:M17” End Sub

请注意，您需要将此代码放入要限制滚动的工作表中。

28.将筛选后的数据复制到新工作簿中

如果您使用的是一个巨大的数据区域，那么过滤器在分割数据时非常有用。

有时，您可能只需要数据区域的一部分。

在这种情况下，您可以使用下面的代码将筛选后的数据快速复制到新工作表中。

Sub CopyFilteredData() If ActiveSheet.AutoFilterMode = False Then Exit Sub End If ActiveSheet.AutoFilter.Range.Copy Workbooks.Add.Worksheets(1).Paste Cells.EntireColumn.AutoFit End Sub

此代码首先检查是否有任何已筛选的数据

否则，它会复制筛选后的数据，插入新工作簿，并将数据粘贴到其中。

29.将所有公式转换为选定数据集中的值

如果要快速将所有具有公式的单元格转换为值，可以使用以下代码：

Sub ConvertFormulastoValues() Dim MyRange As RangeDim MyCell As Range Set MyRange = Selection  For Each MyCell In MyRange  If MyCell.HasFormula Then  MyCell.Formula = MyCell.Value  End If  Next MyCell End Sub

注意这个变化是不可逆的，公式将无法恢复。

或者，你也可以编写一个消息框，显示公式将丢失的警告。这可以防止用户意外运行此宏

30.在单个单元格中获取多个查找值

如果要查找表中的值并在同一单元格中获取所有匹配结果，则需要使用VBA创建自定义函数。

下面是创建了一个公式，类似VLOOKUP。

Function GetMultipleLookupValues(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Result As String For i = 1 To LookupRange.Columns(1).Cells.Count If LookupRange.Cells(i, 1) = Lookupvalue Then Result = Result & ' ' & LookupRange.Cells(i, ColumnNumber) & ',' End If Next iGetMultipleLookupValues = Left(Result, Len(Result) – 1)End Function

注意，这个函数有三个参数：

LookupValue –需要查询的值

LookupRange – 需要查询的区域

ColumnNumber – 提取结果的列号