Run-time Error when adding body parameter to WebRequest in x64

[levitatejay]

I'm getting the following error:
Run-time error '5': Invalid procedure call or argument

When calling AddBodyParameter on a WebRequest

The error occurs on the line:dict_pDictionary.Item(Key) = Value

In:

Public Property Let Item(Key As Variant, Value As Variant)
#If Mac Or Not UseScriptingDictionaryIfAvailable Then
    If Me.Exists(Key) Then
        dict_ReplaceKeyValue dict_GetKeyValue(Key), Key, Value
    Else
        dict_AddKeyValue Key, Value
    End If
#Else
    dict_pDictionary.Item(Key) = Value
#End If
End Property

The key is "propertyName" and the value is "asdasdasd". This error occurs after its already looped through and added some Body Parameters.

The exact same code and input runs fine on 32bit Excel 2010, 2013 and 2016.
The error is occurring for me in x64 Excel 2016

[timhall]

Hmm, not sure why that particular code would be affected by 32- vs 64-bit, The only thing that I can think of is the Variant type (from AddBodyParameter to Let Item) may be causing some weird issue. What is the type of the key/value being passed to AddBodyParameter? I would try the following:

Dim Key, Value ' <- What type are these?

' Explicitly convert key and value to string
Request.AddBodyParameter(CStr(Key), CStr(Value))

I haven't found explicit coercion to string to be necessary, but maybe it'll help. Otherwise, I'll keep looking into it, but I may need a more complete sample.

[levitatejay]

Thanks Tim, I gave that a go but no luck...

I've just done some more testing and I believe its due to passing the Request into a function ByRef. Have you encountered something like this before? Seems to also be related to x64 Excel

Here is my function:

Sub AddBodyParamIfNotEmpty(ByRef Request As WebRequest, ParamName As String, CellName As String)
    If Not IsEmpty(Sheets(SE1.Name).Range(CellName)) And Not IsError(Sheets(SE1.Name).Range(CellName)) Then
                Request.AddBodyParameter ParamName, Sheets(SE1.Name).Range(CellName).Value
        End If
    End If
End Sub

[levitatejay]

Quick replication:

Sub Something()
    Dim Request As New WebRequest
    Request.AddBodyParameter "Test", "Testing"
    QuickTest Request, "Test2", "Testing2"
End Sub

Sub QuickTest(ByRef Request, ParamName As String, ParamValue As String)
    Request.AddBodyParameter ParamName, ParamValue
End Sub

Has error in x64 Excel when attempting to add body parameter to a request passed ByRef but not when adding to request normally

[timhall]

Great sleuthing @levitatejay! I'll look into it and see what I can find.

[levitatejay]

Hi Tim,

I've done some more searching. It looks like the problem is related to an automation issue with the key.

You can see that the key's value is still intact when calling "AddBodyParameter"

But once it goes a level deeper into the Dictionary implementation it loses its value:

Any idea why this is happening/ what I can do to fix it?

[levitatejay]

The problem doesn't appear to be related to passing ByRef as I initially thought.

I can get my code to work by changing my subs declaration:
AddBodyParamIfNotEmpty(ByRef Request As WebRequest, Key As String, Value As String)
from Key As String to Key As Variant

or by changing the the following sub's (from WebRequest class) declaration:
Public Sub AddBodyParameter(Key As Variant, Value As Variant)
from Key As Variant to ByVal Key As Variant

So it appears the reference to the initial Key Variant/String object is being lost somewhere between jumping from the WebRequest Class to the Dictionary Class...

But to make matters more confusing:

• It only occurs in x64 Excel
• The "automation error" only occurs to the Key and not the Value even though they are the same type and go through the exact same process ???

[timhall]

@levitatejay Thanks again for digging so deep into this weird issue. I'll add ByVal shortly to resolve this.