PDA

View Full Version : [SOLVED] Adding to Collections



gsmcconville
03-18-2016, 06:40 AM
Morning everyone.

Can somebody please explain to me why the following code produces different results?


Private Sub Test()Set fso = CreateObject("Scripting.FileSystemObject")
Dim Coll as new Collection, objFile as File
Set objFile = fso.GetFile(Insert filepath here)
Coll.Add(objFile)
Coll.Add objFile
End Sub

In the first collection entry, it is a string object, and in the second collection entry, it is a file object.

Took me a few hours to realize what was going wrong, but can't figure out why the two lines should be added differently?

Thanks

Paul_Hossler
03-18-2016, 07:28 AM
http://dailydoseofexcel.com/archives/2012/05/01/quick-vba-tip-parentheses/



When you put parentheses around an object, VBA evaluates that object and, absent a property, returns the default property. The first Add might error or might not work as expected


Simple example ('TypeName' needs it's own parens)



Option Explicit
Private Sub Test()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim Coll As New Collection, objFile As Object
Set objFile = fso.GetFile("c:\users\daddy\desktop\test.txt")

MsgBox TypeName((objFile))
MsgBox TypeName(objFile)

End Sub

gsmcconville
03-18-2016, 02:51 PM
Thanks Paul, good to know these little things, that in the end can make a huge difference.