VBA学习小结

VBA学习总结 在中断模式下（ctrl+Break键），可以做：1.执行 工具—-选项—-编辑器—-勾选“自动显示数据提示”则当用鼠标悬停在变量或表达式上时，会出现提示窗口，显示其名称和值！2.在监视窗口查看某些表达式的值，方法是：先用鼠标拖黑以选中表达式，再执行 调试—-添加监视，出现“添加监视”对话框。3.在代码窗口中选中表达式—-按shift+F9—-出现”快速监视“对话框，显示该表达式的上下文，表达式及其值。4.按ctrl+G，出现”立即窗口”。它主要有4个作用：（1）.打印信息在窗口中输入Debug.Print 表达式1；表达式2……，即可以显示各表达式的值！2.可以给变量或属性赋值！例如：Form1.BackColor＝255 MaxRows＝503.可以测试过程在此窗口中可以直接输入过程名称和实参以便调用过程。例如： MySub 2*i4.可以检查错误号例如：输入 Error 11按下回车键时，在此窗口中显示11号错误的信息。我常用这个方法来检查错误号！断点在设计和中断模式下都可以设置断点，当程序执行到断点语句时，则中断执行，这时处于中断模式！1.设置断点在代码窗口，用鼠标点击左边区使之出现黑点，代表已经设置断点！也可以把光标移动到选定语句上，按F9.断点语句以粗体字表示，颜色可以由： 工具—选项—编辑器格式—代码颜色—-“断点点文本”选项设定！2.撤销断点：调试—-清除所有断点（ctrl+shift+F9)3.使用Stop语句关闭项目时，断点不被保存，下次调试时需要重新设置。如果多次调试，最好的方法是使用Stop语句，每当遇到它，程序进入中断模式。Stop语句可以保存在代码中，测试完后可以全部取消！监视窗口可以监视表达式，“快速监视”窗口也可以显示表达式的值。立即窗口对表达式求值，给变量和属性赋值。本地窗口显示局部变量的当前值。例如下图自己定义的变量： 单步执行调试程序：1.调试—-逐语句(按快捷键是F8)2.在逐语句执行过程中，遇到调用的过程，不想进入其中停留，按shift+F8,可以“逐过程”执行而不进入！3.跳出过程调试—-跳出（快捷键是ctrl+shift+F8),可以运行当前过程剩余语句代码，转到调用该过程语句的下一个语句去！4.执行特定的语句块这是很重要的一个方法，常用到：在中断模式下，把光标移到要执行的语句行处，执行 调试—设置下一条语句，用它可以设置下次要执行的语句。再把光标移到要停止的语句处，执行 调试—-运行到光标处（快捷键是ctrl+F8)，则可以运行到该语句处！这种方法的优点是可以绕过不想执行的语句块（例如你明确知道某个有问题的语句）。 调用堆栈：应用程序处于中断模式时，显示已被调用过的过程！1.怎么显示调用堆栈的对话框：视图—调用堆栈（快捷键是ctrl+L)2.跟踪嵌套过程在VBA过程中，常常有调用另外某个或某几个过程，模块，窗体。可以用“调用堆栈”从下往上显示已活化过程的项目名称，模块或窗体名称和过程名称。操作方法是：ctrl+L —-用鼠标选中某个过程—–显示。这时，代码窗口显示出该过程，光标处于即将调用下一个过程的调用语句处。此时，按（shift+F9）用“快速监视”或（ctrl+G）立即窗口，可以显示有关变量！3.用“调用堆栈”来检查过程调用自身的能力使用UltreEdit之类的十六进制编辑程序打开.XLS文件，在文本模式下查找“[Host Extender Info]”(也可只查Host)，切换到十六进制模式，将前面的“DBP＝”XXXXXXX…”的DBP关键字改成CBP，将“GC＝”XXXXXXX…”的GC关键字改成CC，使Excel不能识别此二项！存盘即可！！！　　用Excel打开此文件，忽略错误提示，进入VBA编辑器，嘿嘿，密码没有了！做一次存盘操作即可修复错误提示。 Access的VBA工程密码采用无法破解！ Private Sub CommandButton1_Click()CommandButton1.Height = CommandButton1.Height * 1.1CommandButton1.Width = CommandButton1.Width * 1.1End SubVBA如何获取excel活动工作表中所有的chart图表名字,并activate?MS 2009-10-22 20:15:06 阅读336 评论0 字号：大中小 订阅For i = 1 To ActiveSheet.ChartObjects.Count MsgBox ActiveSheet.ChartObjects(i).NameNextActiveSheet.ChartObjects(1).ActivateActiveSheet.ChartObjects(“Chart 1”).Activate请看下面的操作步骤：我就是用这个办法破译excel表中的密码的，你也试一试吧1\打开文件 2\工具—宏—-录制新宏—输入名字如:aa 3\停止录制(这样得到一个空宏) 4\工具—宏—-宏,选aa,点编辑按钮 5\删除窗口中的所有字符(只有几个),替换为下面的内容:(复制吧) 6\关闭编辑窗口 7\工具—宏—–宏,选AllInternalPasswords,运行,确定两次,等2分钟,再确定.OK,密码完全被你看见了!! 内容如下: Public Sub AllInternalPasswords() ‘ Breaks worksheet and workbook structure passwords. Bob McCormick ‘ probably originator of base code algorithm modified for coverage ‘ of workbook structure / windows passwords and for multiple passwords ‘ ‘ Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1) ‘ Modified 2003-Apr-04 by JEM: All msgs to constants, and ‘ eliminate one Exit Sub (Version 1.1.1) ‘ Reveals hashed passwords NOT original passwords Const DBLSPACE As String = vbNewLine & vbNewLine Const AUTHORS As String = DBLSPACE & vbNewLine & _ “Adapted from Bob McCormick base code by” & _ “Norman Harker and JE McGimpsey” Const HEADER As String = “AllInternalPasswords User Message” Const VERSION As String = DBLSPACE & “Version 1.1.1 2003-Apr-04” Const REPBACK As String = DBLSPACE & “Please report failure ” & _ “to the microsoft.public.excel.programming newsgroup.” Const ALLCLEAR As String = DBLSPACE & “The workbook should ” & _ “now be free of all password protection, so make sure you:” & _ DBLSPACE & “SAVE IT NOW!” & DBLSPACE & “and also” & _ DBLSPACE & “BACKUP!, BACKUP!!, BACKUP!!!” & _ DBLSPACE & “Also, remember that the password was ” & _ “put there for a reason. Don’t stuff up crucial formulas ” & _ “or data.” & DBLSPACE & “Access and use of some data ” & _ “may be an offense. If in doubt, don’t.” Const MSGNOPWORDS1 As String = “There were no passwords on ” & _ “sheets, or workbook structure or windows.” & AUTHORS & VERSION Const MSGNOPWORDS2 As String = “There was no protection to ” & _ “workbook structure or windows.” & DBLSPACE & _ “Proceeding to unprotect sheets.” & AUTHORS & VERSION Const MSGTAKETIME As String = “After pressing OK button this ” & _ “will take some time.” & DBLSPACE & “Amount of time ” & _ “depends on how many different passwords, the ” & _ “passwords, and your computer’s specification.” & DBLSPACE & _ “Just be patient! Make me a coffee!” & AUTHORS & VERSION Const MSGPWORDFOUND1 As String = “You had a Worksheet ” & _ “Structure or Windows Password set.” & DBLSPACE & _ “The password found was: ” & DBLSPACE & “$$” & DBLSPACE & _ “Note it down for potential future use in other workbooks by ” & _ “the same person who set this password.” & DBLSPACE & _ “Now to check and clear other passwords.” & AUTHORS & VERSION Const MSGPWORDFOUND2 As String = “You had a Worksheet ” & _ “password set.” & DBLSPACE & “The password found was: ” & _ DBLSPACE & “$$” & DBLSPACE & “Note it down for potential ” & _ “future use in other workbooks by same person who ” & _ “set this password.” & DBLSPACE & “Now to check and clear ” & _ “other passwords.” & AUTHORS & VERSION Const MSGONLYONE As String = “Only structure / windows ” & _ “protected with the password that was just found.” & _ ALLCLEAR & AUTHORS & VERSION & REPBACK Dim w1 As Worksheet, w2 As Worksheet Dim i As Integer, j As Integer, k As Integer, l As Integer Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer Dim PWord1 As String Dim ShTag As Boolean, WinTag As Boolean Application.ScreenUpdating = False With ActiveWorkbook WinTag = .ProtectStructure Or .ProtectWindows End With ShTag = False For Each w1 In Worksheets ShTag = ShTag Or w1.ProtectContents Next w1 If Not ShTag And Not WinTag Then MsgBox MSGNOPWORDS1, vbInformation, HEADER Exit Sub End If MsgBox MSGTAKETIME, vbInformation, HEADER If Not WinTag Then MsgBox MSGNOPWORDS2, vbInformation, HEADER Else On Error Resume Next Do ‘dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 With ActiveWorkbook .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _ Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If .ProtectStructure = False And _ .ProtectWindows = False Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND1, _ “$$”, PWord1), vbInformation, HEADER Exit Do ‘Bypass all for…nexts End If End With Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If If WinTag And Not ShTag Then MsgBox MSGONLYONE, vbInformation, HEADER Exit Sub End If On Error Resume Next For Each w1 In Worksheets ‘Attempt clearance with PWord1 w1.Unprotect PWord1 Next w1 On Error GoTo 0 ShTag = False For Each w1 In Worksheets ‘Checks for all clear ShTag triggered to 1 if not. ShTag = ShTag Or w1.ProtectContents Next w1 If ShTag Then For Each w1 In Worksheets With w1 If .ProtectContents Then On Error Resume Next Do ‘Dummy do loop For i = 65 To 66: For j = 65 To 66: For k = 65 To 66 For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66 For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66 For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126 .Unprotect Chr(i) & Chr(j) & Chr(k) & _ Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) If Not .ProtectContents Then PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _ Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _ Chr(i4) & Chr(i5) & Chr(i6) & Chr(n) MsgBox Application.Substitute(MSGPWORDFOUND2, _ “$$”, PWord1), vbInformation, HEADER ‘leverage finding Pword by trying on other sheets For Each w2 In Worksheets w2.Unprotect PWord1 Next w2 Exit Do ‘Bypass all for…nexts End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next Loop Until True On Error GoTo 0 End If End With Next w1 End If MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER End Sub天有泪，烛有泪，天泪有声，烛泪有形，