CODEWITHSUNDEEP
vba
Francisco Javier Jaimes
Francisco Javier Jaimes

Reputation: 11

Using brackets when creating object in vba excel?

I have copied code from a vba project that I found to go from xml to excel but it gives me an error in my vba project, I have checked the reference libraries.

ruta = LCase(CreateObject([explorador]).BrowseForFolder(0, "selecciona la carpeta a procesar", 0, "").items.Item.Path)

I made the following change and it worked ruta = LCase(CreateObject("shell.application").BrowseForFolder(0, "selecciona la carpeta a procesar", 0, "").Items.Item.Path)

but then it came back to this line

With CreateObject([openFile])

I get the error 13 that the execution times do not match. Variables do not match

I check the variables and they are correctly: unsure:

I don't understand why with the original file it runs smoothly and the replica doesn't. It has been very little I have found it with respect to the syntax of those lines of code when writing it [explorer] and [openFile]

Upvotes: 1

Views: 1197

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

Square brackets in VBA are used for what the language specification calls "foreign identifiers"; they're how you can explicitly invoke a Range object's default member, which is a hidden member named _Default - since VBA identifiers cannot begin with an underscore, doing MsgBox SomeRange._Default would be illegal. So we do MsgBox SomeRange.[_Default] instead and now the code can compile and run. Same with the SomeCollection.[_NewEnum] hidden member, when creating custom collection classes.

When the bracketed identifier doesn't contain any illegal-in-an-identifier characters, then they are purely redundant.

Various VBA hosts also implement some form of Evaluate mechanism; when hosted in Excel, you can do this MsgBox [A1] and you'll get the value of cell A1 from the active worksheet - in this context [A1] is an expression VBA takes and passes to the host application (here Excel), which evaluates it and returns a result to VBA - in this case a Range object reference.

So what CreateObject([explorador]) really does, is really this:

CreateObject(ActiveSheet.Range("explorador").Value)

Except Excel doesn't understand what explorador is referring to (is the sheet supposed to define a explorador named range? I can't imagine why you'd want to do that though), and comes back with what would show up as a #NAME? error on a worksheet, wrapped up in a Variant/Error value:

CreateObject(CVErr(XlErrName)) 'type mismatch error every time!

Lose the square brackets, you'll lose the headache with them!

Assuming explorador and openfile are String variables containing a valid/registered ProgID, CreateObject(explorador) should work as intended.

Consider using either string literals or declared constants with CreateObject: having a layer of indirection here is obscuring exactly what type of COM object is being created. With CreateObject("Scripting.FileSystemObject") can only fail if the Scripting.FileSystemObject isn't in the registry.

Upvotes: 2

Related Questions