Need a function to return 6 different values as array

[Thomasso]

Hi,

I want to build a function that will return information about a business subject based on its VAT ID (using an API).

Example:

Input for a subject with VAT ID 123456789: GetSubjectInfo("123456789")

Expected returns:
GetSubjectInfo(0) = "Company Name"
GetSubjectInfo(1) = "The best Avenue 123"
GetSubjectInfo(2) = "My city"

I have successfully setup the API, so I can easily get this information. But my issue is that I want to build a function that I can use over and over again, therefore I would like it to return all values (7 values to be exact). The best approach seems to be to return an array, but I can't seem to be able to set this up correctly...

This is what I have (based on some Googling):

Option Compare Database
Option Explicit

Public Function ARES_GetSubjectInfo(strICO As String) As Variant

    Dim HTTPreq As Object
    Dim URL As String
    Dim strBody As String
    Dim JSON As Object
    Dim arrReturnValues(0 To 6) As String

    Set HTTPreq = CreateObject("Microsoft.XMLHTTP")
    
    URL = "https://ares.gov.cz/ekonomicke-subjekty-v-be/rest/ekonomicke-subjekty/" & strICO
    
    HTTPreq.Open "GET", URL, False
    HTTPreq.SetRequestHeader "content-type", "application/json"
    
    HTTPreq.Send
    
    Set JSON = JsonConverter.ParseJson(HTTPreq.ResponseText)
    
    arrReturnValues(0) = JSON("obchodniJmeno")
    arrReturnValues(1) = JSON("nazevUlice")
    arrReturnValues(2) = JSON("cisloDomovni")
    arrReturnValues(3) = JSON("cisloOrientacni")
    arrReturnValues(4) = JSON("psc")
    arrReturnValues(5) = JSON("nazevObce")
    arrReturnValues(6) = JSON("kodKraje")
    
    Set HTTPreq = Nothing
    Set JSON = Nothing
    
    ARES_GetSubjectInfo = arrReturnValues

End Function

Unfortunately this returns "type-mismatch" error.



The idea is that I will call this function only once and then be able to work with those 7 respective values anywhere in the application.

Can you please push me in the right direction?
Thank you!

Tomas

[Welshgasman]

I have started using ChatGPT to help others.

Function GetFruits() As Variant
    Dim fruits(2) As String
    fruits(0) = "Apple"
    fruits(1) = "Banana"
    fruits(2) = "Cherry"
    
    GetFruits = fruits
End Function

Sub TestGetFruits()
    Dim myFruits As Variant
    Dim i As Integer


    myFruits = GetFruits()
    
    For i = LBound(myFruits) To UBound(myFruits)
        Debug.Print myFruits(i)
    Next i
End Sub

The other way I would have tried before asking would be using TempVars.

[orange]

Untested, but this sort of approach might be applicable....

' Define the custom type at the top of a standard module (outside any procedure)

Public Type MyInfo
    supplierName As String
    supplierAddr1 As String
    supplierAddr2 As String
    supplierCity As String
    supplierState As String
    supplierZip As String
    supplierEmail As String
End Type

' Function to return a populated MyInfo type
Public Function GetSupplierInfo() As MyInfo
    Dim info As MyInfo
    
    ' Example data - you would normally retrieve this from a table/form or call to a source/your API
    info.supplierName = "Acme Supplies Inc."
    info.supplierAddr1 = "123 Main Street"
    info.supplierAddr2 = "Suite 400"
    info.supplierCity = "Springfield"
    info.supplierState = "IL"
    info.supplierZip = "62704"
    info.supplierEmail = "contact@acmesupplies.com"
    
    GetSupplierInfo = info
End Function

Where I show string values populating the Type structure, these could be like this in your set up.

info.variable = JSON("cisloDomovni")

[Welshgasman]

Another avenue might be a collection?
https://learn.microsoft.com/en-us/of...lection-object

[NoellaG]

I think the best way is to create a class module with a property for each value you want to return.
For each download you can instance a new object from the class, set the properties using the json values