【VBA】创建工作簿自定义函数

最新推荐文章于 2023-09-14 22:21:41 发布

本命雪狮

最新推荐文章于 2023-09-14 22:21:41 发布

阅读量673

点赞数

分类专栏： VBA Excel 文章标签： vba excel

本文链接：https://blog.csdn.net/QQ114152850/article/details/108974415

版权

VBA 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

Excel

4 篇文章 0 订阅

订阅专栏

自定义创建工作簿，可以指定创建的工作簿有几个工作表（有更简单的方法，比如设置Application.SheetsInNewWorkbook = sheet_num后再设置回来）

第二个自定义函数是方便以某一工作表创建后直接赋值工作簿

'创建工作簿，指定有多少个工作表
Function CreateWorkbook(Optional ByVal sheet_num As Integer = 1) As Workbook
    Dim n As Integer, count As Integer, diff As Integer
    Dim wb As Workbook
    
    Set wb = Workbooks.Add
    count = wb.Sheets.count
    If count < sheet_num Then
        Do Until count = sheet_num
            wb.Sheets.Add After:=wb.Sheets(count)
            count = count + 1
        Loop
    Else
      Application.DisplayAlerts = False
        Do Until count = sheet_num
            wb.Sheets(count).Delete
            count = count - 1
        Loop
      Application.DisplayAlerts = True
    End If
    '
    Set CreateWorkbook = wb
End Function

'复制某一工作表创建工作簿
Function CreateWorkbookFromSheet(sh As Worksheet)
  Dim wb As Workbook
  
  Set wb = CreateWorkbook(1)
  sh.Copy After:=wb.Sheets(1)
  Application.DisplayAlerts = False
  wb.Sheets(1).Delete
  Application.DisplayAlerts = True
  Set CreateWorkbookFromSheet = wb
End Function