JSONConverter.bas выдает ошибку компиляции: Sub или Function не определены

Question

Windows 10Excel O365 VBA, импортированный Jsonconverter.bas в проект Modulels, импортированный Dictionary.cls в проект, добавлен справочник по классам для сценариев среды выполнения MS

Могу видеть данные в моем .ResponseText, но как только программа вызывает «Set Json = JsonConverter.ParseJson (http.responseText)», я получаю ошибку компиляции.

Мое первое знакомство с парсингом данных JSON ... так что заранее спасибо за помощь !!

Нужно ли мне что-то делать со словарем, кроме импорта его в раздел «Класс»? Есть ли какая-либо ссылка на него в основном sub() моего скрипта?

Код ниже. Функция ошибки находится ниже этого, а http.responsetext ниже этого

Конечно, оцените помощь по (я уверен) очень простому исправлению моей глупой ошибки. Многому научился с помощью этого первого проекта синтаксического анализа: =)

      Sub getdata()

Dim inJson As Object
Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
Dim http As Object
Set http = CreateObject("WinHttp.WinHttprequest.5.1")
Url = "https://www.printavo.com/api/v1/orders?email=mysite.com&token=UN" & "&query=1005"
http.Open "Get", Url, False
http.send
Set Json = JsonConverter.ParseJson(http.responseText)
Stop: 'never makes it here!
.
.
.
End Sub

выдается ошибка и выделяется первый оператор "Set" после: (никогда не доходит до Stop)

      Public Function ParseJson(ByVal JsonString As String) As Object

Dim json_Index As Long
json_Index = 1

' Remove vbCr, vbLf, and vbTab from json_String
JsonString = VBA.Replace(VBA.Replace(VBA.Replace(JsonString, VBA.vbCr, ""), VBA.vbLf, ""), VBA.vbTab, "")

json_SkipSpaces JsonString, json_Index
Select Case VBA.Mid$(JsonString, json_Index, 1)
Case "{"
    Set ParseJson = json_ParseObject(JsonString, json_Index)
Case "["
    Set ParseJson = json_ParseArray(JsonString, json_Index)
Case Else
    ' Error: Invalid JSON string
    Err.Raise 10001, "JSONConverter", json_ParseErrorMessage(JsonString, json_Index, "Expecting '{' or '['")
End Select
End Function

К вашему сведению

      http.response.text = ? http.responsetext
{"meta":{"page":1,"per_page":25,"total_count":531,"total_pages":22},"data":[{"sales_tax":7.5,"total_untaxed":0.0,"discount_as_percentage":false,"discount":0.0,"customer_id":4902516,"user_id":37796,"orderstatus_id":185896,"public_hash":"a994d28ae3a530f53b0a62430e69","production_notes":"","order_nickname":"Augusta Shirts","approved":false,"approved_name":null,"visual_id":20535,"stats":{"paid":false},"notes":"","created_at":"2021-08-26T16:19:16.309-04:00","updated_at":"2021-08-29T15:09:56.930-04:00","due_date":"2021-08-26T10:00:00.000-04:00","order_total":71.0575,"order_subtotal":66.1,"payment_term_id":null,"delivery_method_id":null,"visual_po_number":"","customer":{"full_name":"James Wi","first_name":"James","last_name":"Wi","company":"","email":"[e-mail]","customer_id":0},"order_addresses_attributes":[{"id":20803016,"name":"Customer Billing","customer_name":"James Wi","company_name":"","address1":"","address2":"","city":"","state":"","state_iso":null,"country":"US","country_iso":
"US","zip":""},{"id":20803017,"name":"Customer Shipping","customer_name":"James Wi","company_name":"","address1":"","address2":"","city":"","state":"","state_iso":null,"country":"US","country_iso":"US","zip":""}],"contractor_profile":null,"expenses":[],"orderstatus":{"name":"Quote","color":"#47A0D9"},"user":{"name":"Chic"},"lineitems_attributes":[{"id":28200912,"style_description":"Augusta Sportswear - Adult Attain Wicking Short-Sleeve T-Shirt","taxable":true,"style_number":"2790","color":"Black","size_other":null,"size_xs":null,"size_s":null,"size_m":null,"size_l":null,"size_xl":null,"size_2xl":null,"size_3xl":3,"total_quantities":3,"goods_status":"","category":" 3-Apparel","unit_cost":115.66},{"id":28200913,"style_description":"Augusta Sportswear - Attain Color Secure® Performance Long Sleeve T-Shirt","taxable":true,"style_number":"2795","color":"Black","size_other":null,"size_xs":null,"size_s":null,"size_m":null,"size_l":null,"size_xl":null,"size_2xl":null,"size_3xl":1,"total_quantities":1,"goods_stat
us":"","category":" 3-Apparel","unit_cost":19.1},{"id":28200914,"style_description":"Sherriff","taxable":true,"style_number":"","color":"","size_other":1,"size_xs":null,"size_s":null,"size_m":null,"size_l":null,"size_xl":null,"size_2xl":null,"size_3xl":null,"total_quantities":1,"goods_status":"","category":" 2-Transfer Services","unit_cost":0.01},{"id":28200915,"style_description":"Franklin Badge","taxable":true,"style_number":"","color":"","size_other":1,"size_xs":null,"size_s":null,"size_m":null,"size_l":null,"size_xl":null,"size_2xl":null,"size_3xl":null,"total_quantities":1,"goods_status":"","category":" 1-Embroidery","unit_cost":0.01},{"id":28200916,"style_description":"","taxable":false,"style_number":"","color":"","size_other":null,"size_xs":null,"size_s":null,"size_m":null,"size_l":null,"size_xl":null,"size_2xl":null,"size_3xl":null,"total_quantities":0,"goods_status":"","category":"0-Convenience fee if paying by credit card","unit_cost":0.01}],"order_fees_attributes":[],"id":7161334,"customer_due_d
ate":"2021-08-26T00:00:00.000-04:00","custom_created_at":"2021-08-26T00:00:00.000-04:00","invoice_date":"2021-08-26T00:00:00.000-04:00","payment_due_date":"2021-08-26T00:00:00.000-04:00","formatted_invoice_date":"08/26/2021","formatted_customer_due_date":"08/26/2021","formatted_payment_due_date":"08/26/2021","formatted_custom_created_at_date":"08/26/2021","url":"https://www.printavo.com/invoices/7161334","amount_paid":0.0,"amount_outstanding":71.0575,"public_url":"https://www.printavo.com/invoice/a995e8b4d28ae3a530f53b0a62430e69","pdf":"https://www.printavo.com/invoice/eff.pdf","workorder":"https://www.printavo.com/invoices/7/workorder","packaging_slip":"https://www.printavo.com/invoices/7/packing_slip"},{"sales_tax":0.0,"total_untaxed":0.0,"discount_as_percentage":false,"discount":0.0,"customer_id":99999,"user_id":9999,"orderstatus_id":196994,"public_hash":"eff","production_notes":"

Order Notes:Â 
Garments coming from XXXXXXX on
PO XX99XX99XX99B:Â 

TLJ763 Cornerstone Duck Cloth Work Jacket:Â 
Navy: XLT-1, 2XLT-1Â 

Garment coming from TrixXxXxXx on PO QQTTWWBBFFSS123456789A:Â 

6000 Lightweight jacket 
Navy: 2XLT-1Â 

Left chest, all garments: SDG Security logo 
Thread colors:Â 
Red 1147, White 

Пожалуйста, дайте мне знать, если у вас возникнут какие-либо вопросы относительно логотипа.

Я заберу, когда закончу.

Спасибо!

      ","order_nickname":"Prtewfgbvc5467843D -  security","approved":false,"approved_name":null,"visual_id":20534,"stats":{"paid":false,"paid_date":"2021-04-14"},"notes":"","created_at":"2021-08-26T15:49:00.048-04:00","updated_at":"2021-08-26T15:50:47.668-04:00","due_date":"2021-08-26T15:45:00.000-04:00","order_total":9.0,"order_subtotal":9.0,"payment_term_id":1817,"delivery_method_id":null,"visual_po_number":"P329D","customer":{"full_name":"Teresa L",
    .
    .
    .
    ]}

Answer 1

Я подозреваю, что в ответе что-то есть, вероятно, поле production_notes. Попробуйте эту упрощенную тестовую программу.

      Sub test
    Dim inJson As Object, obj, s As String, msg As String
    s = "{'meta':{'page':1,'per_page':25,'total_count':531,'total_pages':22}}';"
    Set inJson = JsonConverter.ParseJson(s)
    For Each obj In inJson("meta")
       msg = msg & obj & " : " & inJson("meta")(obj) & vbCrLf
    Next
    MsgBox msg
end sub