excel vba import system.collections.hashmap into一个模块 [英] excel vba import system.collections.hashmap into a module

问题描述

从我的 MS Excel 2010 安装里面我已经打开了 Visual Basic编辑器。 （Tab开发工具 - > Visual Basic）

在 Visual Basic编辑器中，我右键单击项目窗口，并创建了一个模块。 （插入 - >模块）

然后我在代码窗口中键入以下代码：

  Sub myFunction（）
'对我的表单元格做一些事情，不重要
 End Sub 
  

在函数体内编码了很多东西后，我以为我需要一个Hashmap，这需要在给定的功能体内访问。不幸的是，我无法导入执行此操作所需的类：

 导入System.Collections 
 Sub myFunction（）
'对我的表单元格做一些东西，不重要
 End Sub 
  

按F5启动模块时，会出现错误消息。它说（从我的宝石翻译成英文）：编译时错误：程序之外的非法。

当代码结构如上所述时，如何在VBA中导入某些东西？我通常是一个 java或python 人。您还可以重新构建代码，如果该函数仍然执行。

解决方案

在这种情况下，您可以使用

收藏类

或

词典 class

---

收藏 class将内置到VBA中，因此您不需要添加外部引用即可使用它。您可以简单地声明它

  Dim c as Collection 
 Set c = new Collection 
  

集合公开4种方法： add，count，item ，删除，这可能不足以满足您的需求。

From the inside of my MS Excel 2010 installation I have opened the Visual Basic Editor. (Tab Developer Tools -> Visual Basic)

Inside the Visual Basic Editor i right-clicked into the Project Window and created a module. (Insert -> Module)

Then i typed the following code into the Code Window:

Sub myFunction()
    'do some stuff to my table cells, not important
End Sub

After coding a lot of things inside the function body, i thought i was in need for a Hashmap, which needs to be accessed inside that given function body. Unfortunately, i could not import the class needed by doing this:

Imports System.Collections
Sub myFunction()
    'do some stuff to my table cells, not important
End Sub

The error message appears when launching the module by pressing F5. And it says (translated from geman to english by me): "Error while Compiling: illegal outside of a procedure".

How is it possible to have something imported in VBA, when the code is structured like above? I'am usually a java or python person. You can also re-structure the code, if the function still executes.

解决方案

In this case you either use a

Collection class

or

Dictionary class

---

Collection class is built-in into VBA so you do not need to add external references to be able to use it. You can simply declare it

Dim c as Collection
Set c = new Collection

Collection exposes 4 methods: add, count, item, remove so it may not be sufficient for you.

Customizing, expanding Collection class

---

If you want to use something like a HashTable/HashMap then add references to Microsoft Scripting Runtime by clicking Tools and References in the VBE window

then you can use early-binding and intellisense with the Dictionary class

Dim d as Dictionary
Set d = new Dictionary

Or use late-binding

Dim d as Object
set d = CreateObject("Scripting.Dictionary")

I would go for the early-binding (first example - adding references to VBA project) so you can use VBA Intellisense.

You can view Dictionary or Collection class using the Object Browser - simply hit F2 in the VBE window and type in Dictionary or Collection

Useful read about the Dictionary class

这篇关于excel vba import system.collections.hashmap into一个模块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！