Calc Macro: How to store key value pairs

English
, , ,
#1

Hello,

I need to store key value pairs in a macro, namely an error code with a description.
How can I retrieve the description when knowing the error code in Basic?

I have all the data in this format:
ERRORS = [
(“1003”, “Statement is not available.”),
(“1004”, “Statement is incomplete at this time. Please try again shortly.”),
(“1005”, “Settlement data is not ready at this time. Please try again shortly.”),
(“1006”, “FIFO P/L data is not ready at this time. Please try again shortly.”),
(“1007”, “MTM P/L data is not ready at this time. Please try again shortly.”),
(“1008”, “MTM and FIFO P/L data is not ready at this time. Please try again shortly.”),
(“1009”, “The server is under heavy load. Statement could not be generated at this time. Please try again shortly.”),
(“1010”, “Legacy Flex Queries are no longer supported. Please convert over to Activity Flex.”),
(“1011”, “Service account is inactive.”),
(“1012”, “Token has expired.”),
(“1013”, “IP restriction.”),
(“1014”, “Query is invalid.”),
(“1015”, “Token is invalid.”),
(“1016”, “Account in invalid.”),
(“1017”, “Reference code is invalid.”),
(“1018”, “Too many requests have been made from this token. Please try again shortly.”),
(“1019”, “Statement generation in progress. Please try again shortly.”),
(“1020”, “Invalid request or unable to validate request.”),
(“1021”, “Statement could not be retrieved at this time. Please try again shortly.”),
]

Now I want to access the string “statment is not available” when I only know the error code. e.g.1003 in this case.

Up to now my best guess would be to create an array like:

Dim ERRORS()
ERRORS = Array(Array(“1003”, “Statement is not available.”), +_
Array(“1004”, “Statement is incomplete at this time. Please try again shortly.”),+_
Array(“1005”, “Settlement data is not ready at this time. Please try again shortly.”),+_
Array(“1006”, “FIFO P/L data is not ready at this time. Please try again shortly.”),+_
Array(“1007”, “MTM P/L data is not ready at this time. Please try again shortly.”),+_
Array(“1008”, “MTM and FIFO P/L data is not ready at this time. Please try again shortly.”),+_
Array(“1009”, “The server is under heavy load. Statement could not be generated at this time. Please try again shortly.”),+_
Array(“1010”, “Legacy Flex Queries are no longer supported. Please convert over to Activity Flex.”),+_
Array(“1011”, “Service account is inactive.”),+_
Array(“1012”, “Token has expired.”),+_
Array(“1013”, “IP restriction.”),+_
Array(“1014”, “Query is invalid.”),+_
Array(“1015”, “Token is invalid.”),+_
Array(“1016”, “Account in invalid.”),+_
Array(“1017”, “Reference code is invalid.”),+_
Array(“1018”, “Too many requests have been made from this token. Please try again shortly.”),+_
Array(“1019”, “Statement generation in progress. Please try again shortly.”),+_
Array(“1020”, “Invalid request or unable to validate request.”),+_
Array(“1021”, “Statement could not be retrieved at this time. Please try again shortly.”))

and then search for the Error number location.

Thank you very much in advance

Hawki

#2

I don’t understand why you don’t want to keep the data on a sheet.

1. Use Collection.
See attached file.
LibOBasic_09_StructDataTypes_Flat_A4_EN_v101.odt (162.1 KB)

Adding an item with Key:
Dim oCol As New Collection
oCol.Add(sErrDescr, "ErrCode")

Getting an item by Key:
sDescr = oCol("ErrCode")
You don’t have to search for a value like in an array.

2. Try EnumerableMap service.

' Create the map & fill it.
 oMap = com.sun.star.container _
 .EnumerableMap.create("string", "string")  'KeyType (or "short|long"), ValueType

oMap.put(key, value)

oMap.get(key)

More
And more

1 Like
#3

awesome exactly what i was looking for.
Does this work as well with one key and many values? Or do you suggest a better solution as shown below?
e.g.:

’ Create the map & fill it.
oMap = com.sun.star.container _
dim a()
a = array(1,2,3)
.EnumerableMap.create(“string”, “array”) 'KeyType (or “short|long”), ValueType

oMap.put(“some key”, a)

oMap.get(key)

#4

I could be wrong, but Collection object doesn’t work with custom structures, you have to create a VBA class and create instances. Whether it works with arrays, I don’t remember.
Map object definitely works. The array is set like this:
.EnumerableMap.create("string", "[]string")
I could be wrong. There is also the type Any. Try it yourself. I don’t see any examples on the Internet. Very poor. But the Map object did not cause complaints.

#5

The ScriptForge library provides a “Dictionary” class:

"A dictionary is a collection of key-item pairs
The key is a case-insensitive string
Items may be of any type
Keys and items can be retrieved, counted, updated, and much more."

Read ScriptForge.Dictionary service

#6

A small performance test (writing and reading 10,000 keys).

Sub testSpeed
  Dim myDict As Variant
  Dim oEnumerableMap
  Dim nmax as Long, i as Long, key as String, t as Long, s as String, j As Long, v
 
  Nmax=10000
  key=String(50, "a") 
   
  For j=1 To 2
    If j=1 Then
      s="ScriptForge Dictionary:"
      GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
      myDict = CreateScriptService("Dictionary")
    Else
      s=s & Chr(10) & "EnumerableMap:"
      oEnumerableMap = com.sun.star.container.EnumerableMap.create("string", "any")     
    End If  
 
    t=GetSystemTicks()
    For i=1 To nmax
      If j=1 Then
        myDict.add key & i, i
      Else
        oEnumerableMap.put key & i, i
      End If            
    Next i   
 
    s=s & Chr(10) & "Write: " & (GetSystemTicks()-t)
    
    t=GetSystemTicks()
 
    For i=1 To nmax
      If j=1 Then
        v=myDict.Item(key & i)
      Else 
        v=oEnumerableMap.get(key & i)
      End If  
    Next i   
 
    s=s & chr(10) & "Read " & (GetSystemTicks()-t)
  Next j  
  Msgbox s
End Sub

Result (time in milliseconds):

ScriptForge Dictionary:
Write: 154737
Read 152078
EnumerableMap:
Write: 152
Read 129
2 Likes
#7

I already mentioned, that use of String(50, "a") & i as a key is definitely not a sensible test, but rather a worst-case scenario. I didn’t test the performance myself, so it’s possible that EnumerableMap beats ScriptForge Dictionary in more realistic cases, too - but that needs testing, and without that, this test is simply misleading.

#8

Results for the same test (with a very short key).

  ' key=String(50, "a") 
  key="a"

ScriptForge Dictionary:
Write: 8144
Read 4912
EnumerableMap:
Write: 147
Read 126
1 Like
#9

I am sorry if it’s off-topic; I am not familiar with the implementation of EnumerableMap, so could you tell if it is case-insensitive or case-sensitive; and if it is insensitive, does it work with non-ASCII characters properly?

#10

Service EnumerableMap is case-sensitive.
It is converted to insensitive using constructs like:

 key="A"
 oEnumerableMap.put lcase(key), 0 
 v=oEnumerableMap.get(lcase(key))

In applications, we use the class module based on the EnumerableMap service, where the case of the key is controlled using a property.

#11

Does it also perform that fast when lcase is used?
(Yes, it will, because internally, it will use faster sensitive comparisons …)

#12

Changed test with long key:

key=String(50, "A")
' ...
oEnumerableMap.put lcase(key) & i, i
' ...
v=oEnumerableMap.get(lcase(key) & i)

EnumerableMap:
Write: 173
Read 150
1 Like
#13

ScriptForge dict is based on VBA Collection and key is insensitive there.

#14

@sokol92 sorry to bother you with this, but you seem to know a lot about basic. If you have a second, could you perhaps check out my second (and much more tricky) question please?

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.