VBA能做什么?下面来看一下dQ/dV快速计算程序的演示动图:
使用说明:
(1)请注意使用20次以内的平滑次数,过大易导致过度平滑而丧失峰信息;
(2)请将自己的充电数据、放电数据覆盖在A~D列,单击“dQ/dV”按钮,立即得到微分数据(K~N列);
(3)复制K~N列数据到Origin中绘图;
后续会发布dQ程序的编程教程。或许,你想要这个Excel dQ/dV模板文件了。文末有领取方式。
1.4 本地窗口调试
当一个复杂的科学计算程序运行时,本地窗口为程序员提供了各类资源,包括每个变量的取值变化,因此本地窗口是调试代码、单步执行、更改或查找问题的重要工具。首次运行VBE的界面中并不一定显示本地窗口,可通过单击菜单“视图→本地窗口”显示出来。
利用本地窗口查询运行程序各参数的方法:在某个过程内单击激活该过程,不停地按F8单步执行代码,可在本地窗口中查看各参数的取值发生了变化。当我们不再需要单步调试时,可以单击停止按钮。
提问二:
提问三:
从以上对话可以看出,ChatGPT不仅给出完美的编程代码,而且还教我们怎样去调试,甚至细致到能温馨提示注意事项。
ChatGPT回答的3个代码模块,解决了我最近在开发的族谱程序所需Excel批量信息录入问题。例如我们添加姓名、字辈、父亲姓名后,Excel表格自动填充族号、世代、母亲等信息。演示动图如下:
利用VBA编程使Excel变得智能起来。例如当输入父亲姓名后,VBA自动查找父亲的族号,并与排行数字拼接为新的族号,自动填入当前族人的族号单元格中。再如,当输入字辈“正”字之后,VBA自动查找世代的数字,并自动填入世代的单元格中。
对“Person”表的编程代码如下:
Sub FindCorrespondingNumber(ROW)
\'定义变量
Dim i As Long
Dim j As Long
Dim strName As String
Dim strNumber As String
\'Sheets(\"Person\").Range(\"A:C\").Locked = False \'将A、B、C列的锁定属性设为True
\'ActiveSheet.Unprotect \'解除保护锁定
\'循环遍历Person中的姓名
\'For i = 2 To Sheets(\"Person\").Cells(Rows.Count, 2).End(xlUp).ROW
\'获取姓名
strName = Sheets(\"Person\").Cells(ROW, 7).Value
\'查找对应编号
For j = 2 To Sheets(\"Person\").Cells(Rows.Count, 2).End(xlUp).ROW
If Sheets(\"Person\").Cells(j, 4).Value = strName Then
\'获取编号
strNumber = Sheets(\"Person\").Cells(j, 2).Value & Sheets(\"Person\").Cells(ROW, 6).Value
\'将编号写入Sheet2的A列
Sheets(\"Person\").Cells(ROW, 2).Value = strNumber
\'自动递增ID
Sheets(\"Person\").Cells(ROW, 1).Value = Sheets(\"Person\").Cells(ROW - 1, 1).Value + 1
\'自动填入母亲姓氏
Sheets(\"Person\").Cells(ROW, 9).Value = Sheets(\"Person\").Cells(j, 10).Value
\'自动填入“男”
Sheets(\"Person\").Cells(ROW, 5).Value = \"男\"
\'自动增加排行
Sheets(\"Person\").Cells(ROW, 6).Value = Sheets(\"Person\").Cells(ROW - 1, 6).Value + 1
Exit For
End If
Next j
Beep
\'Next i
\'Sheets(\"Person\").Range(\"A:C\").Locked = True \'将A、B、C列的锁定属性设为True
\'ActiveSheet.Protect \'保护工作表
End Sub
Sub FindZiBei(ROW)
\'定义变量
Dim i As Long
Dim j As Long
\'字辈:輅谭汝仲修,甲常福如谭,万(谭)守(谭)大元心,行斯道宗正,宏儒绍文明,志学传世远,祖德发祥龄,新铭开第泽,作述振家声,崇本光先代,安邦定永清。
Dim ZiBeiKu(57)
ZiBeiKu(0) = \"辂\": ZiBeiKu(1) = \"辂谭\": ZiBeiKu(2) = \"汝\": ZiBeiKu(3) = \"仲\": ZiBeiKu(4) = \"修\"
ZiBeiKu(5) = \"甲\": ZiBeiKu(6) = \"常\": ZiBeiKu(7) = \"福\": ZiBeiKu(8) = \"如\": ZiBeiKu(9) = \"如谭\"
ZiBeiKu(10) = \"万\": ZiBeiKu(11) = \"万谭\": ZiBeiKu(12) = \"守\": ZiBeiKu(13) = \"守谭\": ZiBeiKu(14) = \"大\": ZiBeiKu(15) = \"元\": ZiBeiKu(16) = \"心\"
ZiBeiKu(17) = \"行\": ZiBeiKu(18) = \"斯\": ZiBeiKu(19) = \"道\": ZiBeiKu(20) = \"宗\": ZiBeiKu(21) = \"正\"
ZiBeiKu(22) = \"宏\": ZiBeiKu(23) = \"儒\": ZiBeiKu(24) = \"绍\": ZiBeiKu(25) = \"文\": ZiBeiKu(26) = \"明\"
ZiBeiKu(27) = \"志\": ZiBeiKu(28) = \"学\": ZiBeiKu(29) = \"传\": ZiBeiKu(30) = \"世\": ZiBeiKu(31) = \"远\"
ZiBeiKu(32) = \"祖\": ZiBeiKu(33) = \"德\": ZiBeiKu(34) = \"发\": ZiBeiKu(35) = \"祥\": ZiBeiKu(36) = \"龄\"
For i = 0 To 36
If Sheets(\"Person\").Cells(ROW, 8).Value = ZiBeiKu(i) Then
Sheets(\"Person\").Cells(ROW, 3).Value = i + 1
Exit For
End If
Next i
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 4 And Target.ROW >= 2 Then\'当第4列的某一行单元格数据改变时,执行本过程。\'当第4列的某一行单元格数据改变时
\'自动填入“男”
Sheets(\"Person\").Cells(Target.ROW, 5).Value = \"男\"
\'自动增加排行
Sheets(\"Person\").Cells(Target.ROW, 6).Value = Sheets(\"Person\").Cells(Target.ROW - 1, 6).Value + 1
End If
If Target.Column = 7 And Target.ROW >= 2 Then
Application.EnableEvents = False \'避免引起循环
FindCorrespondingNumber (Target.ROW) \'调用FindCorrespondingNumber过程
Application.EnableEvents = True \'恢复事件处理
End If
If Target.Column = 8 And Target.ROW >= 2 Then
Application.EnableEvents = False \'避免引起循环
FindZiBei (Target.ROW) \'调用查找字辈
Application.EnableEvents = True \'恢复事件处理
End If
If Target.Column = 5 And Target.ROW >= 2 Then
Application.EnableEvents = False \'避免引起循环
If Sheets(\"Person\").Cells(Target.ROW, 5).Value = 1 Then
Sheets(\"Person\").Cells(Target.ROW, 5).Value = \"男\"
Else
Sheets(\"Person\").Cells(Target.ROW, 5).Value = \"女\"
End If
FindZiBei (Target.ROW) \'调用查找字辈
Application.EnableEvents = True \'恢复事件处理
End If
End Sub
公众号后台回复:dQ