CODEWITHSUNDEEP
excelvbafunction
bgordon
bgordon

Reputation: 149

Is it possible to group function calls in VBA?

I have a function that looks something like:

Public Function GetData(DataType As String) As String

    Dim Client As New WebClient
    Client.BaseUrl = "http://url/to/get/data"

    Dim Response As New WebResponse
    Set Response = Client.GetJson(DataType)

    GetInstruments = Response.Data("data")

End Function

It's a simple HTTP GET that returns a value based on an argument.

My problem is that I'm trying to execute this function for many different cells at once in Excel (i.e. =GetData(A$1)) that leads to hundreds of HTTP calls which is very slow.

Is there a way that in VBA that I am able to intercept function calls so I can then make a single and quick HTTP call and then return all the data at once?

Upvotes: 2

Views: 91

Answers (1)

Michał Turczyn
Michał Turczyn

Reputation: 37377

You can use global variables in a module to cache and reuse alread downloaded data.

First easy to digest example using simple Collection:

Private someCollection As Collection

Public Function GetData() As Integer
    ' Make sure that data is already read/created
    If someCollection Is Nothing Then
        ' If we didn't get any data, then get it
        Set someCollection = New Collection
        someCollection.Add (1)
    End If
    ' Get data :)
    GetData = someCollection(1)
End Function

Now, applying this logic to your problem you could do:

Private Response As WebResponse

Public Function GetData(DataType As String) As String
    ' You can alter check to see if URL has changed.
    ' In order to do that just store URL in some global variable
    If Response Is Nothing Then
        Dim Client As New WebClient
        Client.BaseUrl = "http://url/to/get/data"
        Set Response = Client.GetJson(DataType)
    End If
    GetInstruments = Response.Data("data")
End Function

Of course, all this code goes into module.

Upvotes: 1

Related Questions