0

I am using excel 2013. I have a large sheet which consist of a list of Customers and their information. When I add new customers to this spreadsheet it fills most information by posting the CustomerID to our server, the server returns the customer information in a Json string which is then parsed. A specific function returns the information required i.e. "=Json_get_email(Userid)" will return the email address. All this work very well and is relatively user friendly for the staff in my company to use.

The problem arises when an autofilter is applied. Even if no function is volatile in this, applying an autofilter causes the spreadsheet to recalculate all the functions and what was efficient and quick for a customer or a few customers is now slowing down the spreadsheet like crazy.

I am turning to you to know if there is any way of preventing my functions to be calculated every time a filter is applied.

my best,

Fabien

Fabien
  • 11
  • 1
  • 4
  • I think this question is fairly similar to yours http://stackoverflow.com/questions/12808009/run-vba-code-automatically-after-running-a-filter The answer references this article https://www.experts-exchange.com/articles/2773/Trapping-a-change-to-a-filtered-list-with-VBA.html – jcarroll Jun 28 '16 at 15:06
  • Modify your `Json_get_email` function to cache the return value for each input value - re-use the cached value if present instead of re-fetching the data from the server. Should be pretty safe since the return value should be fairly fixed for any given input. – Tim Williams Jun 28 '16 at 16:25

2 Answers2

2

Something like this will make your sheet much faster:

Function Json_get_email(arg)

    Static dict As Object '<< persists between calls

    If dict is nothing then set dict = Createobject("scripting.dictionary")

    If not dict.exists(arg) Then
        'have not seen this value of arg before
        '...get the return "email" value for 'arg' here
        dict.add arg, email
    End If

    Json_get_email = dict(arg) 'return the cached value

End Function

Should be no problem caching the return email values between calls which use the same argument value.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • That is a very good idea, i'll try to implement this solution and get back to tell you if functional. – Fabien Jun 30 '16 at 09:22
0

Here is the solution I implemented. I want to share it as I have seen that many people had the same issue with UDFs.

It is not perfect but makes it a lot faster as it avoids connecting to the server and parsing the string each time.

I created a public array of booleans indexed by User_ID, 

Public Names_in_List(100000000 To 104000000) As Boolean

  Function JSon_CustomerName2(UserID As String) As String
    If Names_in_List(UserID) = False Then
      'The Function Has not yet been run for this ID
      '... Do whatever
      Names_in_List(UserID) = True 'Update the status
    Else
      JSon_CustomerName2 = Application.Caller.value 'Reuse the value that was in the cell.
    End If
  End Function

As always, I had to trade memory for speed but with booleans its only one bit per user.

Many thanks @Tim for your helpful insight.

Fabien
  • 11
  • 1
  • 4