Binding for FileDialog

bluto32

New Member
Joined
Jan 5, 2011
Messages
37
I would like to understand the correct syntax for late binding and early binding with FileDialog.
A few Google searches have shown the following snippets of code for other objects, such as an instance of Outlook:

VBA Code:
Sub Outlook1()
    'Late binding.
    'No reference to library needed and no Intellisense.
    Dim olApp As Object
    Set olApp = CreateObject("Outlook.Application")
End Sub

Sub Outlook2()
    'Early binding.
    'Needs reference to "Microsoft Outlook 16.0 Object Library". Intellisense works.
    Dim olApp As Outlook.Application
    Set olApp = New Outlook.Application
End Sub

Sub Outlook3()
    'Quite commonly seen and seems to work as early binding.
    'Needs reference to "Microsoft Outlook 16.0 Object Library". Intellisense works.
    'But... inconsistent syntax (mishmash of 1 and 2 above) and frowned upon.
    Dim olApp As Outlook.Application
    Set olApp = CreateObject("Outlook.Application")
End Sub

However... I am not sure how to use binding correctly with FileDialog.
Here is what I have tried:

VBA Code:
Sub Dialog1()
    'Late binding - is this ok?
    Dim Dialog As Object
    Set Dialog = Application.FileDialog(msoFileDialogFilePicker)
End Sub

Sub Dialog2()
    'Early binding attempt - doesn't compile (can't use "FileDialog" with New).
    Dim Dialog As FileDialog
    Set Dialog = New FileDialog
End Sub

Sub Dialog3()
    'This appears to work as early binding: Intellisense works.
    'But it looks like the "inconsistent syntax / mishmash" example mentioned in "Outlook3".
    Dim Dialog As FileDialog
    Set Dialog = Application.FileDialog(msoFileDialogFilePicker)
End Sub

Any advice on the correct syntax here for late/early binding with FileDialog much appreciated.
I'm not sure why it doesn't behave the same way as for Outlook.Application.
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

bluto32

New Member
Joined
Jan 5, 2011
Messages
37
Thank you, Crystalyzer: that seems to agree with the late binding code in Dialog1 above.
But all early bindings I've seen for FileDialog have looked like the code in Dialog3, which doesn't seem consistent with the proper way to do it for other objects.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
Occasionally there is some confusion or misconception about early binding and late binding. To be clear and for the sake of completeness, it is not about what libraries are referenced in your VBA project, it's about how your code is written. Your code determines wether an object type binds at compile time or at run-time.

Regarding your FileDialog example (dialog2), although a FileDialog is an object, it cannot be "New'ed". The compiler starts to argue here because it recognizes the object type due to the reference to the MS Excel xx.x Object Library. That library tells the compiler that the FileDialog object type is a property of the Excel.Application object. And that's a wonderful gesture by the compiler because in case of typos it saves you from a possible run-time error 438 (member not found). Btw, have you ever tried to clear that library's checkbox within VBE's References dialog?

Back to your query, some examples below. You'll notice that in the late-bound code only member calls are made against a general Object interface, instead of a specific interface known at compile time.

VBA Code:
' EARLY-bound Code
Dim ws As Worksheet
Set ws = Application.Worksheet("SomeSheet")

Dim xl As Excel.Application
Set xl = New Excel.Application

Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)



' LATE-bound code
Dim ws As Object
Set ws = Application.Worksheet("SomeSheet")

Dim xl As Object
Set xl = CreateObject("Excel.Application")

Dim fd As Object
Set fd = Application.FileDialog(msoFileDialogFilePicker)

' without a reference to the Microsoft Office xx.x Object Library the compiler has no access
' to the MsoFileDialogType enumeration so we have to pass the required value
Dim fd As Object
Set fd = Application.FileDialog(3)
 
Solution

bluto32

New Member
Joined
Jan 5, 2011
Messages
37

ADVERTISEMENT

Thank you so much for your explanation and further examples, particularly with regard to unchecking all libraries and having to pass a numerical parameter. Also, I had mistakenly assumed that the "Set" line should always take a completely different syntax when comparing early and late bindings.

One final question to help me further my understanding: why is the code in "Outlook3" frowned upon? i.e. What is wrong with using CreateObject("Outlook.Application") rather than New Outlook.Application in the Set statement? Both seem equally successful in applying early binding.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
You are welcome.

What is wrong with using CreateObject("Outlook.Application") rather than New Outlook.Application in the Set statement? Both seem equally successful in applying early binding.
There's nothing wrong with that and yes, both are able in applying early binding since they perform exactly the same actions. The result returned by the first one however, depends on whether Outlook is present on the target machine or not. Some folks believe that CreateObject can somehow magically create an object out of the blue, even if the library doesn’t exist. They are very wrong then.

The difference between these two: by using CreateObject the compiler does not need any further information of any type library at all to be able to compile your code. The compiler simply assumes that the coder knows exactly what he is doing and thus offers no assistance to avoid program errors. The New Outlook.Application at the other hand, requires a reference to the corresponding type library, otherwise the compiler will refuse to compile the code, thereby offering its assistance to produce some more robust code.

As said, late binding has little to do with CreateObject and whether or not a library is referenced, it's all about the manner in which the object variables are declared. So if you're intended to copy a range's value into a dictionary for whatever reason, the "ultimate" late-bound code will look like the code below. Trained eyes immediately see what is or will go wrong here. Untrained eyes are invited to paste this code into a fresh new workbook and to watch step by step (F8 key) what happens.

VBA Code:
Sub bluto32()

    ' Option Explicit enabled, LATE-BOUND code, no compile errors
    ' each member call will be resolved at run-time

    Dim Xl As Object, Wb As Object, Sht As Object, Rng As Object, Dic As Object, c As Object

    Set Xl = VBA.CreateObject("Excel.Aplication")
    Set Dic = VBA.CreateObject("Scripting.Dictionary")

    Set Wb = Xl.ActivWorkbook
    Set Sht = Wb.Sheeets(1)
    Set Rng = Sht.Range("$A$1").Offsel(1, 0).Resize(100, 0)
    
    For Each c In Rng
        Dic.Add c.Value
    Next c
End Sub
 

bluto32

New Member
Joined
Jan 5, 2011
Messages
37
Thanks again for taking the time to reply with such a detailed explanation; it was very informative and is much appreciated.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,596
Office Version
  1. 2013
Platform
  1. Windows
Glad my post was helpful.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,965
Messages
5,767,366
Members
425,407
Latest member
Mike Lescai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top