Repository navigation
unnessary vba.Len() calls #292
Description
your are calling "vba.Len()" function at every iteration of while loop in 'Json_SkipSpaces' Sub Procedure.
imagine if json string length is 1M+ Characters. then it could slow down the performance significantly. i added new variable to the
ParseJson() function -> json_Length
Public Function ParseJson(ByVal JsonString As String) As Object
' Remove vbCr, vbLf, and vbTab from json_String
JsonString = VBA.Replace(VBA.Replace(VBA.Replace(JsonString, VBA.vbCr, ""), VBA.vbLf, ""), VBA.vbTab, "")
Dim json_Index As Long, json_Length As Long
json_Index = 1
json_Length = Len(JsonString)
json_SkipSpaces JsonString, json_Index, json_Length
Select Case VBA.Mid$(JsonString, json_Index, 1)
Case "{"
Set ParseJson = json_ParseObject(JsonString, json_Index, json_Length)
Case "["
Set ParseJson = json_ParseArray(JsonString, json_Index, json_Length)
Case Else
' Error: Invalid JSON string
Err.Raise 10001, "JSONConverter", json_ParseErrorMessage(JsonString, json_Index, "Expecting '{' or '['")
End Select
End Functionand then pass this variable to json_Skipspaces procedure and to other procedures where it needed.
this improve the performance significantly
old:
Private Sub json_SkipSpaces(json_String As String, ByRef json_Index As Long)
' Increment index to skip over spaces
Do While json_index > 0 And json_Index <= VBA.Len(json_String) And VBA.Mid$(json_String, json_Index, 1) = " "
json_Index = json_Index + 1
Loop
End Subnew:
Private Sub json_SkipSpaces(json_String As String, ByRef json_Index As Long, ByRef json_Length As Long)
' Increment index to skip over spaces
Do While json_Index <= json_Length And VBA.Mid$(json_String, json_Index, 1) = " "
json_Index = json_Index + 1
Loop
End Sub
Activity
Nick-vanGemeren commentedon Jun 28, 2026
VBA uses counted strings rather than null-terminated strings as in C/Perl/... The
Lenfunction just has to pick up the string length and so is very fast.You seem to have spotted that
json_Indexwill always be > 0 injson_SkipSpaces.The
Mid$function already includes a check on the string length and will return an empty string if the loop gets to the end.So the loop can become:
It may be advisable to keep one length check in the procedure (after the loop) to detect malformed JSON strings, something like: