类模块 mBook
用于工作簿的操作。
版本号 Version:1.0
最终更新日期 Last Update Date:2021-04-01
作者 Author:JunYi
网址 Link:https://gitee.com/junyii/vba-code-base
全面革新:
一年多以来,mBook
的使用场景并不多,里面的几个方法看似有用但实际没有什么应用场景,于是决定对其进行革新。以下:
更新内容 Updates 1.0 2021-04-01
True
改为 False
(修改思想:用到这个函数必然后面的操作都是连续的,弹出提示会打断连续,降低用户体验)False
(修改思想:在获得工作簿对象后,依然可以继续对其进行进一步操作。)False
更新内容 Updates 0.7 2021-03-27:
更新内容 Updates 0.6 2021-06-07:
更新内容 Updates 0.5 2021-5-29:
更新内容 Updates 0.4 2021-3-3:
更新内容 Updates 2020-12-29:
更新内容 Updates 2020-12-26:
更新内容 Updates 2020-12-23:
类模块 mBook
用于工作簿的操作。
下边是一些在测试时,需要在外部定义的公共变量和对象。
Option Explicit
Dim mBook As New mBook
Dim FilePath As String
Dim WB As Workbook
Dim Sht As Worksheet
Dim Rng As Range
下边是一些测试中用到的自定义函数和公共常量。
Public Const EngMode As Boolean = True ' 英文模式
Public Const mName As String = "JunYi Code Base"
' 坏消息
Public Function BadMsg(Message As String)
MsgBox Message, vbCritical, mName
End Function
' 好消息
Public Function GoodMsg(Message As String)
MsgBox Message, vbInformation, mName
End Function
新建Workbook工作簿。由 Property Get
创建,返回一个工作簿对象。
Property Get CreateWorkbook(mPath As String, Optional ShowMsg As Boolean = False, Optional CloseAfterCreate As Boolean = False, Optional mShowScreen As Boolean = False) As Workbook
False
False
False
Sub Book_CreateWorkbook()
FilePath = ThisWorkbook.path & "\测试" & "\测试.xlsx"
Set WB = mBook.CreateWorkbook(FilePath, True, , True)
End Sub
创建成功: ![[Pasted image 20201226124326.png]]
创建失败(文件已存在): ![[Pasted image 20201226124246.png]]
另存为Workbook工作簿,可用于备份重要的表格。
Sub SaveAsWorkbook(mPath As String, mWorkbook As Workbook, Optional mSheetIndex As String = "", Optional mSeparator As String = ",", Optional ShowMsg As Boolean = True)
Sub Book_SaveAsWorkbook()
FilePath = ThisWorkbook.path & "\测试" & "\另存为测试.xlsx"
Set WB = ThisWorkbook
mBook.SaveAsWorkbook FilePath, WB
End Sub
另存成功: ![[Pasted image 20201226130039.png]]
打开Workbook工作簿。由 Property Get
创建,返回一个Workbook工作簿对象。
Property Get OpenWorkbook(mPath As String, Optional mShowScreen As Boolean = True) As Workbook
Sub Book_OpenWorkbook()
FilePath = ThisWorkbook.path & "\测试" & "\测试.xlsx"
Set WB = mBook.OpenWorkbook(FilePath) ' 因为OpenWorkbook有返回值,必须这么写
End Sub
set 工作簿对象变量 = mBook.OpenWorkbook(路径)
的形式。通过对话框打开Workbook工作簿。由 Property Get
创建,返回一个Workbook工作簿对象。
Property Get OpenWorkbookDialog() As Workbook
Sub Book_OpenWorkbookDialog()
Set WB = mBook.OpenWorkbookDialog
End Sub
![[Pasted image 20201229132245.png]]
set 工作簿对象变量 = mBook.OpenWorkbook(路径)
的形式。获得所有数据表名。由 Property Get
创建,返回字符串: SheetName1,SheetName2, ...
,可以用 Split
函数拆分。
Property Get GetSheetName(mWorkbook As Workbook) As String
Sub Book_GetSheetName()
GoodMsg mBook.GetSheetName(ThisWorkbook)
End Sub
执行结果:
![[Pasted image 20201229191844.png]]
自由获取数据表数据。从其他工作簿中复制数据到当前工作簿。
由 Property Get
创建,返回字符串: SheetName1,SheetName2, ...
,可以用 Split
函数拆分。
Sub CopyFromWorkbook(Optional mPath As String = "", Optional mSheetIndex = "", Optional mPasteRange As Range = Nothing, Optional mShowScreen As Boolean = True, Optional mCopyValue As Boolean = True)
True
。True
。Sub Book_CopyFromWorkbook()
mBook.CopyFromWorkbook
End Sub
执行结果:
![[Pasted image 20201229192215.png]] ![[Pasted image 20201229192233.png]] ![[Pasted image 20201229192316.png]] ![[Pasted image 20201229192459.png]]
注意:
mSelect
。NumberToLetter
。GetSheetName
。此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。