c# - 无法分配给从 VBA 消耗的 C# 类中的 Hashtable 项
问题描述
我编写了一个 C# 类 (KvpHash) 用于在 VBA 中使用,它围绕 Hashtable 提供了额外的有用功能。在我的 VBA 代码中,我有一个用于 KvpHash 类的广泛的 Rubberduck 测试套件,它显示了该类的所有功能都按预期工作,除了我无法更改项目的值这一事实。
从 VBA 我收到错误消息
424 '需要对象'
在 C# 类中,接口代码是
dynamic this[dynamic Key] { get; set; }
并且实现是
public dynamic this[dynamic Key]
{
get
{
return MyKvpHash[Key];
}
set
{
MyKvpHash[Key] = value;
}
}
其中 MyKvpHash 定义为
private Hashtable MyKvpHash = new Hashtable();
如果我将 mscorelib 引用添加到 VBA,我可以直接在 VBA 中创建一个哈希表,完全可以在其中更改哈希表中项目的值。
我很感激关于我在 C# 代码中做错了什么导致 object required 错误的指针。
编辑:添加示例 VBA 代码
使用本机哈希表
Public Sub TestHashtable()
' requires reference to mscorlib.dll
Dim myHt As Hashtable
Set myHt = New Hashtable
myHt.Add 5, "Hello5"
myHt.Add 10, "Hello10"
Debug.Print myHt.Item(10)
Debug.Print myHt.Item(5)
' the next line works as expected
myHt.Item(10) = "A new world"
Debug.Print myHt.Item(10)
End Sub
给出输出
Hello10
Hello5
A new world
使用我的 KvpHash 类(HashTable 的包装器)
Public Sub TestKvpHash()
Dim myHt As VBAExtensions.KvpHash
' KvpHash is a C# wrapper for a System.Collections.HashTable
Set myHt = New KvpHash
myHt.AddByKey 5, "Hello5"
myHt.AddByKey 10, "Hello10"
Debug.Print myHt.Item(10)
Debug.Print myHt.Item(5)
' The next line produces error 424
myHt.Item(10) = "A new world"
Debug.Print myHt.Item(10)
End Sub
给出输出
Hello10
Hello5
然后以 424 错误停止。
编辑以根据要求添加完整的 C# 代码。
似乎没有文件托管,而且我没有其他提供链接的方法,所以我在下面插入了相关代码。该代码最初基于字典,但当我第一次发现无法分配给项目时,我将其更新为 Hashtable。该开关没有改变我的代码的行为。请注意,我不是专业程序员,提供的代码基本上是我第一次涉足 C#。通常,我编写 Word VBA 宏供自己使用。
// VBAExtensions
//
// C# Library module for VBA
using System;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
namespace VBAExtensions
{
/// <summary>
/// Enum for accessing the kvphash structure returned by Method Cohorts
/// </summary>
public enum CohortType
{
/// <summary>1 = the keys in A plus keys in B that are not shared</summary>
KeysInAAndOnlyB = 1,
/// <summary>2 = the Keys from B in A where B has a different value to A</summary>
KeysInAandBWithDifferentValues,
/// <summary>3 = the keys that are only in A and only in B</summary>
KeysNotInAandB,
/// <summary>4 = the keys that are inA and B </summary>
KeysInAandB,
/// <summary>5 = the keys in A only </summary>
KeysInAOnly,
/// <summary>6 = the keys in B only</summary>
KeysInBOnly
}
/// <summary>
/// KvpHash is a C# class for VBA which implements a Key/Value HashTable
/// The object is a morer flexible version of the Scripting.Dictionary
/// </summary>
[Guid("30F9294B-11B4-4D91-9D7C-7FF02ADB3F11")]
[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface IKvpHash
{
/// <summary>
/// Returns/Sets the "Value" specified by "Key" (i) of a Key/Value Pair
/// </summary>
/// <param name="Key"></param>
/// <returns>Type used in Set statement (C# dynamic)</returns>
dynamic this[dynamic Key] { get; set; }
/// <summary>
/// Adds "Value" to the KvpHash using an integer (VBA Long) Key.
/// The integer key is based on the first available integer greater than or
/// equal to the Count of the KvpHash
/// </summary>
/// <param name="Value"></param>
void AddByIndex(dynamic Value);
/// <summary>
/// Populates this KvpHash using AddByIndex for each character in the string
/// </summary>
/// <param name="this_string"></param>
void AddByIndexAsChars(string this_string);
/// <summary>
/// Pupulates this KvpHash using AddByIndex for each substring in this_string delineated by this_seperator
/// </summary>
/// <param name="this_string"></param>
/// <param name="this_seperator"></param>
void AddByIndexAsSubStr(string this_string, string this_seperator = ",");
/// <summary>
/// Pupulates a KvpHash using AddByIndex for each array item
/// </summary>
/// <param name="this_array"></param>
void AddByIndexFromArray(dynamic this_array);
/// <summary>
/// Adds "Value" to the KvpHash with a key pf "Key"
/// </summary>
/// <param name="Key"></param>
/// <param name="Value"></param>
void AddByKey(dynamic Key, dynamic Value);
/// <summary>
/// Groups the keys of the two KvpHash
/// </summary>
/// <param name="ArgKvpHash"></param>
/// <returns>An array of 6 KvpHash
/// keys in a {1,2,3,4,5,6}
/// keys in b {1,2,3,6,7,8}
/// 1 = the keys in A plus keys in B that are not shared {1,2,3( from A),4,5,6,7,8}
/// 2 = the Keys from B in A where B has a different value to A {3( from B) if value is different}
/// 3 = the keys that are only in A and only in B {4,5,7,8}
/// 4 = the keys that are in A and B {1,2,3,6}
/// 5 = the keys in A only {4,5}
/// 6 = the keys in B only {7,8}
/// </returns>
KvpHash Cohorts(KvpHash ArgKvpHash);
/// <summary>
/// The number of key/vaue pairs in the KvpHash
/// </summary>
/// <returns>Long</returns>
int Count();
///// <summary>
///// Return the IEnumerator interface for KvpHash
///// </summary>
///// <returns>IEnumerator</returns>
//IEnumerator GetEnumerator();
/// <summary>
/// Gets the "Key" for the first ocurrence of "Value" in the KvpHash.
/// </summary>
/// <param name="Value"></param>
/// <returns>Key</returns>
dynamic GetKey(dynamic Value);
/// <summary>
/// Returns a variant array of the Keys of the KvpHash
/// </summary>
/// /// <returns>Variant Array</returns>
dynamic[] GetKeys();
/// <summary>
/// Returns a variant array of the values of the KvpHash
/// </summary>
/// <returns>Variant Array</returns>
dynamic[] GetValues();
/// <summary>
/// True if the "Key" exists in the keys of the KvpHash
/// </summary>
/// <param name="Key"></param>
/// <returns>Boolean</returns>
bool HoldsKey(dynamic Key);
/// <summary>
/// True if the "Value" exists in the values of the KvpHash
/// </summary>
/// <param name="Value"></param>
/// <returns>Boolean</returns>
bool HoldsValue(dynamic Value);
/// <summary>
/// True if the KvpHash holds 0 key/value pairs
/// </summary>
/// <returns>Boolean</returns>
bool IsEmpty();
/// <summary>
/// True if the KvpHash holds one or more key/value pairs
/// </summary>
/// <returns>Boolean</returns>
bool IsNotEmpty();
/// <summary>
/// True is the "Key" is not found in the keys of the KvpHash
/// </summary>
/// <param name="Key"></param>
/// <returns>Boolean</returns>
bool LacksKey(dynamic Key);
/// <summary>
/// True if the "Value" is not found in the values of the KvpHash
/// </summary>
/// <param name="Value"></param>
/// <returns>Boolean</returns>
bool LacksValue(dynamic Value);
/// <summary>
/// Reverses the Key/Value pairs in a KvpHash
/// </summary>
/// <returns>New KvpHash where:
/// KvpHash.Value(1) = KvpHash Unique values as Value/Key pairs
/// KvpHash.Value(2) = KvpHash Non unique values as Key/Value pairs</returns>
KvpHash Mirror();
/// <summary>
/// Removes the Key/Value pair spacified by "Key" from the KvpHash
/// </summary>
/// <param name="Key"></param>
void Remove(dynamic Key);
/// <summary>
/// Removes all Key/Value pairs from the KvpHash
/// </summary>
void RemoveAll();
/// <summary>
/// Returns true if the Values in KvpHash are unique.
/// </summary>
/// <returns>Boolean</returns>
bool ValuesAreUnique();
/// <summary>
/// Returns true if the Values in KvpHash are not unique.
/// </summary>
/// <returns>Boolean</returns>
bool ValuesAreNotUnique();
}
[Guid("87E5A539-FDB3-40D0-9CCD-C817F9893C08")]
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDual)]
public class KvpHash : IKvpHash, IEnumerable
{
private Hashtable MyKvpHash = new Hashtable();
public dynamic this[dynamic Key]
{
get
{
return MyKvpHash[Key];
}
set
{
MyKvpHash[Key] = value;
}
}
public void AddByIndex(dynamic Value)
{
int my_index = MyKvpHash.Count + 1;
while (MyKvpHash.ContainsKey(my_index))
{
my_index++;
}
MyKvpHash.Add(my_index, Value);
}
public void AddByIndexAsChars(string this_string)
{
int my_index = MyKvpHash.Count + 1;
while (MyKvpHash.ContainsKey(my_index))
{
my_index++;
}
char[] MyArray = this_string.ToCharArray();
MyKvpHash.Clear();
for (int i = 0; i <= MyArray.GetUpperBound(0); i++)
{
//KvpHash uses ordinal indexes
MyKvpHash.Add(i + 1, MyArray[i].ToString());
}
}
public void AddByIndexAsSubStr(string this_string, string this_seperator = ",")
{
int my_index = MyKvpHash.Count + 1;
while (MyKvpHash.ContainsKey(my_index))
{
my_index++;
}
string[] MyArray = this_string.Split(this_seperator.ToArray());
for (int i = 0; i <= MyArray.GetUpperBound(0); i++)
{
//KvpHash uses ordinal indexes
MyKvpHash.Add(i + 1, MyArray[i]);
}
}
public void AddByIndexFromArray(dynamic this_array)
{
int my_index = MyKvpHash.Count + 1;
while (MyKvpHash.ContainsKey(my_index))
{
my_index++;
}
for (int i = 0; i <= this_array.GetUpperBound(0); i++)
{
//KvpHash uses ordinal indexes
MyKvpHash.Add(i + 1, this_array[i]);
}
}
public void AddByKey(dynamic Key, dynamic Value)
{
MyKvpHash.Add(Key, Value);
}
public KvpHash Cohorts(KvpHash ArgKvpHash)
{
KvpHash ResultKvpHash = new KvpHash();
// VBA reports object not set error if the resuly KvpHash are not newed
for (int i = 1; i < 7; i++)
{
ResultKvpHash.AddByKey(i, new KvpHash());
}
foreach (DictionaryEntry MyKvpHashPair in MyKvpHash)
{
// A plus unique in B
ResultKvpHash[1].AddByKey(MyKvpHashPair.Key, MyKvpHashPair.Value);
if (ArgKvpHash.LacksKey(MyKvpHashPair.Key)) // problem is here
{
// In A only or in B only
ResultKvpHash[3].AddByKey(MyKvpHashPair.Key, MyKvpHashPair.Value);
// In A only
ResultKvpHash[5].AddByKey(MyKvpHashPair.Key, MyKvpHashPair.Value);
}
else
{
// In A and In B
ResultKvpHash[4].AddByKey(MyKvpHashPair.Key, MyKvpHashPair.Value);
}
}
foreach (dynamic MyKey in ArgKvpHash.GetKeys())
{
// B in A with different value
if (ResultKvpHash[1].LacksKey(MyKey)) // Result 0 will contain all of A
{
ResultKvpHash[1].AddByKey(MyKey, ArgKvpHash[MyKey]);
ResultKvpHash[3].AddByKey(MyKey, ArgKvpHash[MyKey]);
ResultKvpHash[6].AddByKey(MyKey, ArgKvpHash[MyKey]);
}
else
{
if (ResultKvpHash[1][MyKey] != ArgKvpHash[MyKey])
{
ResultKvpHash[2].AddByKey(MyKey, ArgKvpHash[MyKey]);
}
}
}
return ResultKvpHash;
}
public Int32 Count()
{
return MyKvpHash.Count;
}
public bool IsEmpty()
{
return MyKvpHash.Count == 0;
}
public bool IsNotEmpty()
{
return !IsEmpty();
}
public IEnumerator GetEnumerator()
{
foreach (DictionaryEntry my_pair in MyKvpHash)
{
yield return my_pair.Value;
}
}
public dynamic GetKey(dynamic Value)
{
return this.Mirror()[1][Value];
}
public dynamic[] GetKeys()
{
return (dynamic[]) MyKvpHash.Keys;
}
public dynamic[] GetValues()
{
return (dynamic[]) MyKvpHash.Values;
}
public bool HoldsKey(dynamic Key)
{
return MyKvpHash.ContainsKey(Key);
}
public bool HoldsValue(dynamic Value)
{
return MyKvpHash.ContainsValue(Value);
}
public bool LacksKey(dynamic Key)
{
return !HoldsKey(Key);
}
public bool LacksValue(dynamic Value)
{
return !HoldsValue(Value);
}
public KvpHash Mirror()
{
KvpHash MyResult = new KvpHash();
MyResult.AddByIndex(new KvpHash());
MyResult.AddByIndex(new KvpHash());
foreach (DictionaryEntry my_pair in MyKvpHash)
{
if (MyResult[1].LacksKey(my_pair.Value))
{
MyResult[1].AddByKey(my_pair.Value, my_pair.Key);
}
else
{
MyResult[2].AddByKey(my_pair.Key, my_pair.Value);
}
}
return MyResult;
}
public void Remove(dynamic Key)
{
MyKvpHash.Remove(Key);
}
public void RemoveAll()
{
MyKvpHash.Clear();
}
public bool ValuesAreUnique()
{
return MyKvpHash.Count == ((dynamic[]) MyKvpHash.Values).Distinct().Count();
}
public bool ValuesAreNotUnique()
{
return !ValuesAreUnique();
}
}
}
解决方案
@Freeflow 如果您在 Word 模块中更改 myHt 定义,它将正常并且可以正常工作。
Public Sub TestKvpHash()
Dim myHt As Object
' KvpHash is a C# wrapper for a System.Collections.HashTable
Set myHt = New VBAExtensions.KvpHash
' Rest of code
推荐阅读
- java - 绘制矢量图标
- entity-framework - 这些 ORM 不只是名称不同的 ADO.NET 吗?
- linux - 有什么方法/替代 Linux 中的 readlink -f 来处理文件夹名称中存在的空格?
- python - 使用仅在运行时已知的字段名称更新数据类中的字段
- c++ - 在 C++ 中使用向量实现堆栈
- python - Python中的Websocket消息处理程序
- java - Hibernate BasicExtractor 不记录任何内容
- wordpress - htaccess 更改默认语言而不更改 url
- php - 如何使用 Passport 在 phpunit 测试中对用户进行身份验证
- security - 我正在尝试对网站使用目录遍历攻击