之前就有了解过WPS Office的宏功能,并且mark了一下,昨天偶然百度到了门路,这里把一些过程简单总结一下,分享给大家。
1.vba简介
Visual Basic for Applications(VBA)是Visual Basic的一种宏语言,是微软开发出来在其桌面应用程序中执行通用的自动化(OLE)任务的编程语言。主要能用来扩展Windows的应用程式功能,特别是Microsoft Office软件。也可说是一种应用程式视觉化的Basic脚本。该语言于1993年由微软公司开发的的应用程序共享一种通用的自动化语言——–Visual Basic For Application(VBA),实际上VBA是寄生于VB应用程序的版本。
2.所需软件:
WPS Office (10.1.0.7224)
也就是目前2018年4月14日安装的最新版本,由此可以看出,和wps版本无关~
vba提取自WPS2012专业增强版
下载地址网上很多,贴出来我在百度云盘保存的
链接:https://pan.baidu.com/s/1HuBHlDmBfYH3zpvNo9c8yg 密码:xr8j
进阶技能:VB语言
4.收藏资料
链接:https://pan.baidu.com/s/1lxn5OKdBzkJwQ9ptmURFLg 密码:bdug
链接:https://pan.baidu.com/s/1dIVRJ0w0IOmQjYMx3b5-Ew 密码:tjra
链接:https://pan.baidu.com/s/1_Mb2WdzetVNAnZfgf4KuyA 密码:bcbp
5.简单入门操作
- 简单使用
1. 创建宏
首先注意:保存的文件格式是Microsoft Office Excel 2007 启用宏的工作簿 (.xlsm),否则将出现以下提示
Range("A1").Value = "Hello,Macro!"
意思是给单元格A1赋值为”Hello,Macro!”
2. Msgbox
MsgBox是Visual Basic和VBS中的一个函数,功能是弹出一个对话框,等待用户单击按钮,并返回一个Integer值表示用户单击了哪一个按钮。
“MsgBox”即为“Message Box”的缩写,在英语中意为“信箱”。
『例1』单击按钮弹出对话框删除所有内容
step1:创建命令按钮,开发工具->命令按钮,在工作表合适位置拉出按钮
Dim answer As Integeranswer = MsgBox("要清空工作表么?", vbYesNo + vbQuestion, "清空工作表")If answer = vbYes Then Cells.ClearContentsElse 'do nothingEnd If
运行即可看到效果,在工作表中随便输入随机数据,单击按钮即可实现清除工作表的效果
step3:拓展
看到下拉框里面的内容否?可以将动作设置成这些,根据命名就可以看出作用;另外,需要做其他效果搜一下Msgbox,看看相关函数就能模仿出其他效果。
Dim myValue As VariantmyValue = InputBox("输个啥?", "InputBox函数", 1)Range("A1").Value = myValue
step1:创建按钮,输入代码,运行调试
3.工作簿和工作表对象
4. Range对象
5. 变量
6. IF Then语句
7. 循环
Dim i As Integeri = 1Do Until i > 6 Cells(i, 1).Value = 20 i = i + 1Loop
Dim i As LongColumns(1).Font.Color = vbBlackFor i = 1 To Rows.Count If Cells(i, 1).Value < Range("D2").Value And Not IsEmpty(Cells(i, 1).Value) Then Cells(i, 1).Font.Color = vbRed End IfNext i
实际上是把A列中下一个在B中不存在的数据不断添加到B列中,遇到空数据直接跳出,不复制。(功能放到一个命令按钮里面)
Dim toAdd As Boolean, uniqueNumbers As Integer, i As Integer, j As IntegerCells(1, 2).Value = Cells(1, 1).ValueuniqueNumbers = 1toAdd = True'第一个数字总是'唯一的',直接拷贝过去For i = 2 To Sheet1.Range("a65536").End(xlUp).Row'Sheet1.Range("A65536").End(xlUp).Row意思是从A65536向上找到最后一个非空单元格,返回其行号 If Cells(i, 1).Value <> "" Then '如果单元格非空,则继续 For j = 1 To uniqueNumbers '遍历新的一列数据,判断是否重复,重复则false to add,不重复跳出 If Cells(i, 1).Value = Cells(j, 2).Value Then toAdd = False End If Next j '跳出的都是true to add的,拷贝过去即可,拷完一个新列行号增一 If toAdd = True Then Cells(uniqueNumbers + 1, 2).Value = Cells(i, 1).Value uniqueNumbers = uniqueNumbers + 1 End If toAdd = True End IfNext i
Dim i As Integer, j As Integer, temp As Integer, rng As RangeSet rng = Range("A1").CurrentRegionFor i = 1 To rng.Count For j = i + 1 To rng.Count If rng.Cells(j) < rng.Cells(i) Then 'swap numbers temp = rng.Cells(i) rng.Cells(i) = rng.Cells(j) rng.Cells(j) = temp End If Next jNext i
Dim tempString As String, tempInteger As Integer, i As Integer, j As IntegerFor i = 1 To 5 Cells(i, 2).Value = WorksheetFunction.RandBetween(0, 1000)Next iFor i = 1 To 5 For j = i + 1 To 5 If Cells(j, 2).Value < Cells(i, 2).Value Then tempString = Cells(i, 1).Value Cells(i, 1).Value = Cells(j, 1).Value Cells(j, 1).Value = tempString tempInteger = Cells(i, 2).Value Cells(i, 2).Value = Cells(j, 2).Value Cells(j, 2).Value = tempInteger End If Next j
例如:5个项目的权重,数值和限制已给定。
Dim limit As Double, weight As Double, value As Double, totalWeight As Double, maximumValue As DoubleDim i, j, k, l, m As IntegerDim weighti, weightj, weightk, weightl, weightm As DoubleDim valuei, valuej, valuek, valuel, valuem As Doublelimit = Range("D6").valuemaximumValue = 0weighti = Range("B2").valueweightj = Range("C2").valueweightk = Range("D2").valueweightl = Range("E2").valueweightm = Range("F2").valuevaluei = Range("B3").valuevaluej = Range("C3").valuevaluek = Range("D3").valuevaluel = Range("E3").valuevaluem = Range("F3").valueFor i = 0 To 1 For j = 0 To 1 For k = 0 To 1 For l = 0 To 1 For m = 0 To 1 weight = weighti * i + weightj * j + weightk * k + weightl * l + weightm * m value = valuei * i + valuej * j + valuek * k + valuel * l + valuem * m If value > maximumValue And weight <= limit Then Range("B4").value = i Range("C4").value = j Range("D4").value = k Range("E4").value = l Range("F4").value = m totalWeight = weight maximumValue = value End If Next m Next l Next k Next jNext iRange("B6").value = totalWeightRange("B8").value = maximumValue
9. 字符串操作
分离字符串
Dim fullname As String, commaposition As Integer, i As Integer
For i = 2 To 7
fullname = Cells(i, 1).Value
commaposition = InStr(fullname, “,”)
Cells(i, 2).Value = Mid(fullname, commaposition + 2)Cells(i, 3).Value = Left(fullname, commaposition - 1)
Next i
10. 日期和时间
MsgBox Now
'新建一个宏,写这样一句就得出当前时间弹出框
11. 事件
12. 数组
13. 函数和子函数
14. 用户窗体
15. ActiveX控件
If CheckBox1.Value = True Then Range("B4").Value = 1If CheckBox1.Value = False Then Range("B6").Value = 0
TextBox1.Text = "数据录入成功!"
一个清除文本框信息
TextBox1.Value = ""
将列表框链接到单元格A5,设计模式右键单击列表框->属性->LinkedCell填A5
With Sheet1.ListBox1 .AddItem "Turkey" .AddItem "Tokyo" .AddItem "Paris"End With
清除(我把清除都放在了cls按钮里,方便)
ListBox1.Clear
效果
If OptionButton1.Value = True Then Range("A3").Value = 233
If OptionButton2.Value = True Then Range("A6").Value = 666
Range("A5").Value = SpinButton1.Value
属性设置
16.应用对象
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As IntegermyFile = Application.DefaultFilePath & "\sales.csv"Set rng = SelectionOpen myFile For Output As #1For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count cellValue = rng.Cells(i, j).Value If j = rng.Columns.Count Then Write #1, cellValue Else Write #1, cellValue, End If Next jNext iClose #1
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As IntegermyFile = "C:\Users\Randolph\Desktop\testWriteIn.txt"'myFile = Application.GetOpenFilename()Open myFile For Input As #1 Do Until EOF(1) Line Input #1, textline text = text & textline LoopClose #1posLat = InStr(text, "ID")posLong = InStr(text, "密码")Range("A1").Value = Mid(text, posLat + 4, 4)'后一个数是找到数据的长度,数据可以包括空格;前一个数是拷贝数据的起始字符位置,即从这行的第第个字符开始复制'当然,每行第一个是0Range("A2").Value = Mid(text, posLong + 4, 13)'这里实验发现一个汉字算一个字母,若第二个数长于我们所需要的数据,将把下次出现的其他数据录入进来
用到的测试txt文件信息:
Some information here..ID: aPig密码: isAkindOfHAHASome more information here..
联系客服