vba的auto_open中，方法range作用于对象_global时失败

lolifishme 2021-03-31 04:39:44

前几天接了个写vba的单子，要求是打开一个excel工作表就要自动执行，我写的代码如下：

Private Sub Auto_Open()

    Dim Obj As Object, sn As String

    For Each Obj In GetObject("WinMgmts:").InstancesOf("Win32_BaseBoard")

    sn = Obj.SerialNumber

    Next

    

    If Range("A24") = "NC程序名" Then

        If sn = "K716333294" Then

            Call Work

        Else

            MsgBox "设备认证失败！"

        End If

    End If

End Sub

```
我将它放进加载项，发生了这样的错误：方法range作用于对象_global时失败
指向了

If Range("A24") = "NC程序名" Then

我怀疑是宏在自动运行时，excel表格还未完成加载。
请问大佬们如何解决

...全文

1518 2 打赏收藏转发到动态举报

写回复

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

不懂别说哎 2021-04-15

打赏
举报

回复

VBA我不是很熟悉，但是我想问下，你这段代码指明了工作表了么？没发现指明工作表的语句

阿麦 2021-04-14

打赏
举报

回复

如果“我怀疑是宏在自动运行时，excel表格还未完成加载。”，就等加载完再运行一次看看

SQL+数据透视表+VBA 使数据透视表走向更灵活，更智能，更适用。这个是我和师傅一撇首度合作，他提供了文件并提出了要求，我帮他实现其效果下面从几个方面解释一下： 1、功能一个源文件和一个通过用SQL查询生成的数据透视表将源文件拖到电脑的任意位置，甚至将文件名也改掉，用VBA配上代码和窗体找到文件，数据透视表仍然能够正常工作 2、套用现在来讲讲怎么使做出来的东东适应大家的需要 2、1 用OLE DB窗口引用工作表或写SQL语句，因为用这个方法同VBA相通，copy下来代码区的的语句 2、2 打开透视表文件，将透视表中的字段全部拖出来，也就是变成一个空数据透视表。右击下面工作表图标或者工具》宏》visual basic 编辑器，点击模块看到代码区 2、3 将2、1步骤copy的语句commandtext的数据Array中的引号中 .CommandText = Array(" ") 可能不同版本会有一些差别，同时SQL语句中如果添加了文本生成新字段，双引号要成对翻倍如："出库" AS 表单选项要改成 ""出库"" AS 表单选项 2、4 语句太长的处理：在代码区如果你想好看一些，你可以插入“ _”来换行，当然不能插在一个单词或自动名等中间。 2、5 将文件存盘，重新打开就会有了数据，你可以将字段拖入数据透视表中，创建你自己的数据透视表， 2、6 这样文件就可以使用，相信VBA的引导不用教就可以交给别人使用了下面附上代码，包含3个区： 1、工作簿去，打开文件时工作 Private Sub Workbook_Open() Dim OP If Dir(Sheets("path").Range("A1")) = "" Then OP = MsgBox("源文件已被移走，请选择下列选项" + Chr(10) + "1、选择是，重新输入文件全名" + Chr(10) + "2、选择否，打开原有的数据透视表" + Chr(10) + "3、选择取消，关闭文件", vbYesNoCancel, "Scarlett温馨提示") If OP = vbYes Then UserForm1.Show End If If OP = vbNo Then ActiveWorkbook.Close True End If If OP = vbCancel Then Exit Sub End If Else Call refreshpv End If End Sub 2、窗体区，实现文件的查找 Private Sub CommandButton1_Click() Dim fopen As FileDialog Set fopen = Application.FileDialog(msoFileDialogFilePicker) fopen.Show TextBox1.Value = fopen.SelectedItems(1) Set fopen = Nothing End Sub Private Sub CommandButton2_Click() If InStr(TextBox1.Value, ".") > 0 Then Sheets("path").Range("A1") = TextBox1.Value Call refreshpv unload me Else MsgBox "文件名要带路径含后缀的文件名", "Scarlett_88温馨提示" TextBox1.SetFocus End If End Sub Private Sub CommandButton3_Click() Unload Me End Sub Private Sub TextBox1_Change() End Sub Private Sub UserForm_Activate() End Sub Private Sub UserForm_Click() TextBox1.Value = Sheets("path").Range("A1") End Sub 3、模块区，实现SQL语句的地址更新和刷新数据透视表的数据源 Sub refreshpv() With ActiveSheet.PivotTables("数据透视表1").PivotCache .Connection = Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Sourc

Table of Contents | Index Programming Excel with VBA and .NET Preface Part I: Learning VBA Chapter 1. Becoming an Excel Programmer Section 1.1. Why Program? Section 1.2. Record and Read Code Section 1.3. Change Recorded Code Section 1.4. Fix Misteakes Section 1.5. Start and Stop Section 1.6. View Results Section 1.7. Where's My Code? Section 1.8. Macros and Security Section 1.9. Write Bug-Free Code Section 1.10. Navigate Samples and Help Section 1.11. What You've Learned Chapter 2. Knowing the Basics Section 2.1. Parts of a Program Section 2.2. Classes and Modules Section 2.3. Procedures Section 2.4. Variables Section 2.5. Conditional Statements Section 2.6. Loops Section 2.7. Expressions Section 2.8. Exceptions Section 2.9. What You've Learned Chapter 3. Tasks in Visual Basic Section 3.1. Types of Tasks Section 3.2. Interact with Users Section 3.3. Do Math Section 3.4. Work with Text Section 3.5. Get Dates and Times Section 3.6. Read and Write Files Section 3.7. Check Results Section 3.8. Find Truth Section 3.9. Compare Bits Section 3.10. Run Other Applications Section 3.11. Control the Compiler Section 3.12. Not Covered Here Section 3.13. What You've Learned Chapter 4. Using Excel Objects Section 4.1. Objects and Their Members Section 4.2. Get Excel Objects Section 4.3. Get Objects from Collections Section 4.4. About Me and the Active Object Section 4.5. Find the Right Object Section 4.6. Common Members Section 4.7. Respond to Events in Excel Section 4.8. The Global Object Section 4.9. The WorksheetFunction Object Section 4.10. What You've Learned Chapter 5. Creating Your Own Objects Section 5.1. Modules Versus Classes Section 5.2. Add Methods Section 5.3. Create Properties Section 5.4. Define Enumerations Section 5.5. Raise Events Section 5.6. Collect Objects Section 5.7. Expose Objects Section 5.8. Destroy Objects Section 5.9. Things You Can't Do Section 5.10. What You've Learned Chapter 6. Writing Code for Use by Others Section 6.1. Types of Applications Section 6.2. The Development Process Section 6.3. Determine Requirements Section 6.4. Design Section 6.5. Implement and Test Section 6.6. Integrate Section 6.7. Test Platforms Section 6.8. Document Section 6.9. Deploy Section 6.10. What You've Learned Section 6.11. Resources Part II: Excel Objects Chapter 7. Controlling Excel Section 7.1. Perform Tasks Section 7.2. Control Excel Options Section 7.3. Get References Section 7.4. Application Members Section 7.5. AutoCorrect Members Section 7.6. AutoRecover Members Section 7.7. ErrorChecking Members Section 7.8. SpellingOptions Members Section 7.9. Window and Windows Members Section 7.10. Pane and Panes Members Chapter 8. Opening, Saving, and Sharing Workbooks Section 8.1. Add, Open, Save, and Close Section 8.2. Share Workbooks Section 8.3. Program with Shared Workbooks Section 8.4. Program with Shared Workspaces Section 8.5. Respond to Actions Section 8.6. Workbook and Workbooks Members Section 8.7. RecentFile and RecentFiles Members Chapter 9. Working with Worksheets and Ranges Section 9.1. Work with Worksheet Objects Section 9.2. Worksheets and Worksheet Members Section 9.3. Sheets Members Section 9.4. Work with Outlines Section 9.5. Outline Members Section 9.6. Work with Ranges Section 9.7. Range Members Section 9.8. Work with Scenario Objects Section 9.9. Scenario and Scenarios Members Section 9.10. Resources Chapter 10. Linking and Embedding Section 10.1. Add Comments Section 10.2. Use Hyperlinks Section 10.3. Link and Embed Objects Section 10.4. Speak Section 10.5. Comment and Comments Members Section 10.6. Hyperlink and Hyperlinks Members Section 10.7. OleObject and OleObjects Members Section 10.8. OLEFormat Members Section 10.9. Speech Members Section 10.10. UsedObjects Members Chapter 11. Printing and Publishing Section 11.1. Print and Preview Section 11.2. Control Paging Section 11.3. Change Printer Settings Section 11.4. Filter Ranges Section 11.5. Save and Display Views Section 11.6. Publish to the Web Section 11.7. AutoFilter Members Section 11.8. Filter and Filters Members Section 11.9. CustomView and CustomViews Members Section 11.10. HPageBreak, HPageBreaks, VPageBreak, VPageBreaks Members Section 11.11. PageSetup Members Section 11.12. Graphic Members Section 11.13. PublishObject and PublishObjects Members Section 11.14. WebOptions and DefaultWebOptions Members Chapter 12. Loading and Manipulating Data Section 12.1. Working with QueryTable Objects Section 12.2. QueryTable and QueryTables Members Section 12.3. Working with Parameter Objects Section 12.4. Parameter Members Section 12.5. Working with ADO and DAO Section 12.6. ADO Objects and Members Section 12.7. DAO Objects and Members Section 12.8. DAO.Database and DAO.Databases Members Section 12.9. DAO.Document and DAO.Documents Members Section 12.10. DAO.QueryDef and DAO.QueryDefs Members Section 12.11. DAO.Recordset and DAO.Recordsets Members Chapter 13. Analyzing Data with Pivot Tables Section 13.1. Quick Guide to Pivot Tables Section 13.2. Program Pivot Tables Section 13.3. PivotTable and PivotTables Members Section 13.4. PivotCache and PivotCaches Members Section 13.5. PivotField and PivotFields Members Section 13.6. CalculatedFields Members Section 13.7. CalculatedItems Members Section 13.8. PivotCell Members Section 13.9. PivotFormula and PivotFormulas Members Section 13.10. PivotItem and PivotItems Members Section 13.11. PivotItemList Members Section 13.12. PivotLayout Members Section 13.13. CubeField and CubeFields Members Section 13.14. CalculatedMember and CalculatedMembers Members Chapter 14. Sharing Data Using Lists Section 14.1. Use Lists Section 14.2. ListObject and ListObjects Members Section 14.3. ListRow and ListRows Members Section 14.4. ListColumn and ListColumns Members Section 14.5. ListDataFormat Members Section 14.6. Use the Lists Web Service Section 14.7. Lists Web Service Members Section 14.8. Resources Chapter 15. Working with XML Section 15.1. Understand XML Section 15.2. Save Workbooks as XML Section 15.3. Use XML Maps Section 15.4. Program with XML Maps Section 15.5. XmlMap and XmlMaps Members Section 15.6. XmlDataBinding Members Section 15.7. XmlNamespace and XmlNamespaces Members Section 15.8. XmlSchema and XmlSchemas Members Section 15.9. Get an XML Map from a List or Range Section 15.10. XPath Members Section 15.11. Resources Chapter 16. Charting Section 16.1. Navigate Chart Objects Section 16.2. Create Charts Quickly Section 16.3. Embed Charts Section 16.4. Create More Complex Charts Section 16.5. Choose Chart Type Section 16.6. Create Combo Charts Section 16.7. Add Titles and Labels Section 16.8. Plot a Series Section 16.9. Respond to Chart Events Section 16.10. Chart and Charts Members Section 16.11. ChartObject and ChartObjects Members Section 16.12. ChartGroup and ChartGroups Members Section 16.13. SeriesLines Members Section 16.14. Axes and Axis Members Section 16.15. DataTable Members Section 16.16. Series and SeriesCollection Members Section 16.17. Point and Points Members Chapter 17. Formatting Charts Section 17.1. Format Titles and Labels Section 17.2. Change Backgrounds and Fonts Section 17.3. Add Trendlines Section 17.4. Add Series Lines and Bars Section 17.5. ChartTitle, AxisTitle, and DisplayUnitLabel Members Section 17.6. DataLabel and DataLabels Members Section 17.7. LeaderLines Members Section 17.8. ChartArea Members Section 17.9. ChartFillFormat Members Section 17.10. ChartColorFormat Members Section 17.11. DropLines and HiLoLines Members Section 17.12. DownBars and UpBars Members Section 17.13. ErrorBars Members Section 17.14. Legend Members Section 17.15. LegendEntry and LegendEntries Members Section 17.16. LegendKey Members Section 17.17. Gridlines Members Section 17.18. TickLabels Members Section 17.19. Trendline and Trendlines Members Section 17.20. PlotArea Members Section 17.21. Floor Members Section 17.22. Walls Members Section 17.23. Corners Members Chapter 18. Drawing Graphics Section 18.1. Draw in Excel Section 18.2. Create Diagrams Section 18.3. Program with Drawing Objects Section 18.4. Program Diagrams Section 18.5. Shape, ShapeRange, and Shapes Members Section 18.6. Adjustments Members Section 18.7. CalloutFormat Members Section 18.8. ColorFormat Members Section 18.9. ConnectorFormat Members Section 18.10. ControlFormat Members Section 18.11. FillFormat Members Section 18.12. FreeFormBuilder Section 18.13. GroupShapes Members Section 18.14. LineFormat Members Section 18.15. LinkFormat Members Section 18.16. PictureFormat Members Section 18.17. ShadowFormat Section 18.18. ShapeNode and ShapeNodes Members Section 18.19. TextFrame Section 18.20. TextEffectFormat Section 18.21. ThreeDFormat Chapter 19. Adding Menus and Toolbars Section 19.1. About Excel Menus Section 19.2. Build a Top-Level Menu Section 19.3. Create a Menu in Code Section 19.4. Build Context Menus Section 19.5. Build a Toolbar Section 19.6. Create Toolbars in Code Section 19.7. CommandBar and CommandBars Members Section 19.8. CommandBarControl and CommandBarControls Members Section 19.9. CommandBarButton Members Section 19.10. CommandBarComboBox Members Section 19.11. CommandBarPopup Members Chapter 20. Building Dialog Boxes Section 20.1. Types of Dialogs Section 20.2. Create Data-Entry Forms Section 20.3. Design Your Own Forms Section 20.4. Use Controls on Worksheets Section 20.5. UserForm and Frame Members Section 20.6. Control and Controls Members Section 20.7. Font Members Section 20.8. CheckBox, OptionButton, ToggleButton Members Section 20.9. ComboBox Members Section 20.10. CommandButton Members Section 20.11. Image Members Section 20.12. Label Members Section 20.13. ListBox Members Section 20.14. MultiPage Members Section 20.15. Page Members Section 20.16. ScrollBar and SpinButton Members Section 20.17. TabStrip Members Section 20.18. TextBox and RefEdit Members Chapter 21. Sending and Receiving Workbooks Section 21.1. Send Mail Section 21.2. Work with Mail Items Section 21.3. Collect Review Comments Section 21.4. Route Workbooks Section 21.5. Read Mail Section 21.6. MsoEnvelope Members Section 21.7. MailItem Members Section 21.8. RoutingSlip Members Part III: Extending Excel Chapter 22. Building Add-ins Section 22.1. Types of Add-ins Section 22.2. Code-Only Add-ins Section 22.3. Visual Add-ins Section 22.4. Set Add-in Properties Section 22.5. Sign the Add-in Section 22.6. Distribute the Add-in Section 22.7. Work with Add-ins in Code Section 22.8. AddIn and AddIns Members Chapter 23. Integrating DLLs and COM Section 23.1. Use DLLs Section 23.2. Use COM Applications Chapter 24. Getting Data from the Web Section 24.1. Perform Web Queries Section 24.2. QueryTable and QueryTables Web Query Members Section 24.3. Use Web Services Section 24.4. Resources Chapter 25. Programming Excel with .NET Section 25.1. Approaches to Working with .NET Section 25.2. Create .NET Components for Excel Section 25.3. Use .NET Components in Excel Section 25.4. Use Excel as a Component in .NET Section 25.5. Create Excel Applications in .NET Section 25.6. Resources Chapter 26. Exploring Security in Depth Section 26.1. Security Layers Section 26.2. Understand Windows Security Section 26.3. Password-Protect and Encrypt Workbooks Section 26.4. Program with Passwords and Encryption Section 26.5. Workbook Password and Encryption Members Section 26.6. Excel Password Security Section 26.7. Protect Items in a Workbook Section 26.8. Program with Protection Section 26.9. Workbook Protection Members Section 26.10. Worksheet Protection Members Section 26.11. Chart Protection Members Section 26.12. Protection Members Section 26.13. AllowEditRange and AllowEditRanges Members Section 26.14. UserAccess and UserAccessList Members Section 26.15. Set Workbook Permissions Section 26.16. Program with Permissions Section 26.17. Permission and UserPermission Members Section 26.18. Add Digital Signatures Section 26.19. Set Macro Security Section 26.20. Set ActiveX Control Security Section 26.21. Distribute Security Settings Section 26.22. Using the Anti-Virus API Section 26.23. Common Tasks Section 26.24. Resources Part IV: Appendixes Appendix A. Reference Tables Section A.1. Dialogs Collection Constants Section A.2. Common Programmatic IDs Appendix B. Version Compatibility Section B.1. Summary of Version Changes Section B.2. Macintosh Compatibility About the Author Colophon Index

1 数据错误：如果有时候代码报错，先看看数据是不是有问题因为VBA代码（函数，公式）涉及的数据，如果有问题也会报错因为数据引发的代码报错，这算是丧心病狂的报错吗？例子 range("a;a").specialcells(xlcelltypeformulas,16).select 如果范围内，没有报错单元格，会公式报错例子 excel公式的vlookup 查询数据...

分享：“难道生命这漫长进程中所有的努力和希望，都是为了那飞娥扑火的一瞬间？”“飞蛾并不觉得阴暗，它至少享受了短暂的光明。"

'根据指定字符返回行号 'DataName 要搜索的数据信息 'SearchRowCount 搜索的行高 'SearchColumnCount 搜索的列宽 'RowIndex 开始行 'ColumnIndex 开始列 Public Function ReTurnRowNum(ModuleActiveWorkBook As Workbook, SheetName As String, _ Data...

2,462

社区成员

10,807

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章