Application.Dialogs

The following line of code can be used to display any of the built-in dialog boxes.

bResult = Application.Dialogs(xlBuiltinDialog.xlDialogActivate).Show 

The result returned is True if the user clicked OK and False if the user pressed Cancel.
Most of the built-in dialog boxes accept arguments which correspond to the controls on the dialog box.
You cannot use named arguments when passing parameters to these dialog boxes.
Attempting to display a built-in dialog box in an incorrect context will result in an error.
The dialog box for selecting, changing and inputing database records is ShowDataForm.
Include the "Built-in Dialog Box Arguments List" from the VBA help file

Excel

Dim objDialog As Dialog 

For some reason the following line does not work ??

'Dim objDialog As Application.Dialog
Set objDialog = Application.Dialogs(xlBuiltInDialog.xlDialogOpen) 

For some reason the following line doesn't work

'Set objDialog = Dialogs(xlBuiltInDialog.xlDialogOpen)
objDialog.Show 

Include a table of all the parameters from the VBA help

Word

Dim objDialog As Dialog 
   Set objDialog = Dialogs(wdDialogFileOpen) 

You can use the dialogs property with the wdDialogFileOpen constant to return a dialog object that refers to the (File > Open) dialog box.
The Show method displays and executes the action.
The Display method just displays the dialog box, allowing the user to check the controls before executing.

Dim objDialog As Dialog 
   Set objDialog = Dialogs(wdDialogFileOpen) 
   bReturn = objDialog.Display 
   sFileName = objDialog.Name 
   If bReturn = -1 Then 
      Documents.Open FileName:=sFileName 
   End If 

For more info see the help file.