CODEWITHSUNDEEP
vbaexcel
Martin Dimitrov
Martin Dimitrov

Reputation: 4956

VBA Excel 2010 Editor capitalizes property name and it cannot be accessed

As a response to a POST, the server returns very simple JSON object that can indicate success or failure of the upload.

The JSON object can be either {config_version:N} to indicate success or {exception:Reason} to indicate failure.

I process the response in the following manner:

json = objHTTP.responseText
Set sc = CreateObject("ScriptControl")
sc.Language = "JScript"
Set o = sc.Eval("(" + json + ")")

On Error GoTo Exception:
' try to use config_version property
Util.SetConfigData "version", o.config_version
Exit Function

Exception:
If o.Exception = "config_not_valid" Then
   ' one kind of exception - do accordingly
Else
   ' another kind of exception
End If

The problem that I am facing is that the VBA Excel 2010 editor is capitalizing o.exception to o.Exception! In the Watch window I see the property exception but cannot access it. How can I force the Editor to stop capitalizing the property name? Is there other syntax for accessing properties in VBA?

EDIT: To better illustrate what is happening here are two screen shots.

enter image description here

As you can see, the o has a property called exception. When trying to access it with o.Exception it triggers the 438 error.

enter image description here

Since I have control over the backend, I changed the response to return {Exception:whatever} (cannot do it permanently). Now VBA has no problem picking the property.

How is this possible if the VBA compiler is case-insensitive as suggested by Jean-François Corbett?

Upvotes: 1

Views: 1950

Answers (3)

Jean-François Corbett
Jean-François Corbett

Reputation: 38520

The VBA compiler is case-insensitive, so there is absolutely no difference between o.exception and o.Exception as far as VBA is concerned. The capitalization is not the reason you cannot access the exception property. (EDIT: This last sentence is not true! See @Tim Williams' answer. I stand corrected.)

However, you will have to clarify what you mean by "cannot access". What is config_not_valid? (My hunch is, it's an enum from an external library that has not been properly referenced through early binding.)

Declaring all of your variables using Dim with the proper, specific type may help (and you should always do this anyway). Setting references (Tools > References) to allow early binding may also help. It's hard to give better advice unless you give us more detail about your problem.

That being said, if, for cosmetic purposes only, you want to stop the capitalization from happening in the VBA editor, then have a look at @Tim Williams' and @shahkalpesh's answers. The VBA editor's capitalization scheme can sometimes seem quirky. Declaring all of your variables using Dim will make some of these quirks go away -- but that's really the least reason why you should Dim everything, always.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166366

This is wierd. I can get the lower-case "e" to stick in Tester(), but if I change the declaration of "exception" in Test2() to "Exception", then the VBE automatically changes changes any instances in Tester() to "Exception". Changing back to "exception" has the reverse effect.

Basically you need to make sure you're not using "Exception" anywhere else in your project.

Sub Tester()

    Dim sc, o

    Set sc = CreateObject("ScriptControl")
    sc.Language = "JScript"
    Set o = sc.Eval("({exception:'blahblah'})")

    Debug.Print o.exception 'prints blahblah

End Sub


Sub Test2()
    Dim exception As String
    exception = "blah"
End Sub

EDIT:

If you're still stuck then you can try it this way, leaving your parsed JSON object in the script control instance:

Set sc = CreateObject("ScriptControl")
sc.Language = "JScript"

sc.Eval "var o = eval({exception:'blahblah',message:'oops'})"

Debug.Print sc.Eval("o.exception")     'prints blahblah
Debug.Print sc.Eval("o['exception']")  'same thing...

Debug.Print sc.Eval("o.message")       'prints oops

Upvotes: 6

shahkalpesh
shahkalpesh

Reputation: 33474

Change the label Exception: to except:

except:
If o.exception = config_not_valid Then

Also, change the line of code where it would've used goto Exception to goto except.

Upvotes: 1

Related Questions