0

Recently I have been using the dictionary object to make arrays with unique values only by taking advantage of the .exists method which is simpler than writing a loop through the entire array every time to see if a value exists. Then I get the final array by using the .keys method.

Given that the dictionary object requires a definition for each key, I have gone back and forth on what to set as the definition. Originally I was setting each key's definition to the vbempty constant since I have no use for it anyways. But would the best practice here be to use a Null value because it would take up less memory?

apdm
  • 1,260
  • 1
  • 14
  • 33
  • I think you're looking for a set, rather than a dictionary. – Tripp Kinetics Nov 20 '15 at 15:42
  • I'm not familiar with that. When I google it all I get are results about the keyword `Set` – apdm Nov 20 '15 at 15:43
  • 1
    but a vba doesn't have `set` out of the box as far as I know. Maybe a custom implementation? – TLJ Nov 20 '15 at 15:43
  • I'm going to look into this - but generally speaking, does `Null` take up less space than a constant 0 value? – apdm Nov 20 '15 at 15:44
  • Found a few implementations from this link: http://stackoverflow.com/questions/1309689/hash-table-associative-array-in-vba – TLJ Nov 20 '15 at 15:45
  • Dictionary object has a key and an item. The key should be unique, but the item can be the same or different value for each key. – bilbo_strikes_back Nov 20 '15 at 16:00

1 Answers1

1

vbNullString is the most efficient and should be used when you wish to assign an empty string (or when you wish to assess for an empty string).

Using vbNullString does not use any memory. If you're assigning vbNullString to a variable which previously held a value, it removes any space that was previously being used from the memory. Using "" uses 6 bytes. Not sure how many bytes is used by vbEmpty or Null, but I always use vbNullString.

There is an explanation here and here.

luke_t
  • 2,935
  • 4
  • 22
  • 38
  • Interesting! Although that 2nd link led me to [this](https://support.microsoft.com/en-us/kb/162622) page which says: "Note that using vbNullString is equivalent to passing NULL as the parameter. This is not the same as passing an empty string ("")." So I guess they are both the same, really! – apdm Nov 20 '15 at 16:14
  • Ahh, I didn't see/know that! So yeah, sounds like Null will do the job just as well! – luke_t Nov 20 '15 at 16:55