Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    In Word 2003, the following code works like a charm:

    If Dialogs(wdDialogFileNew).Show = -1 Then
    'some code here
    End If

    This shows the FileNew dialog where you can pick a template and, after clicking OK, a new doc is created based on that template.
    I tried something similar in Excel:

    If Application.Dialogs(xlDialogWorkbookNew).Show = -1 Then
    'some code here
    End If

    This doesn't work as I expected.
    First, it doesn't create a new workbook based on the chosen template. It inserted an extra Sheet in the current workbook. And there was no sign of the custom commandbar that should be created and made visible. (The code to do that works fine in Excel97 for 2 years.)

    Second, Excel insists that Application. should be in front of Dialogs(...). If not, it complains with:

    Compile error: Sub or Function not defined

    Third, if this code is called when there's NO workbook already open, this error occurs:

    Runtime error 1004: Property Show of class Dialog cannot be (found / loaded ?)
    (in Dutch: Eigenschap Show van klasse Dialog kan niet worden opgehaald)

    What am I doing wrong?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    Jan,

    To display the Templates dialog, you need

    <code>Application.Dialogs(xlDialogNew).Show</code>

    This one will also work if no workbook is open.

    Note: in general, Excel VBA is more finicky than Word VBA, for reasons not known to me. You may also have noticed that IntelliSense is much less Intelli in Excel. For example, no list of properties and methods pops up if you type <code>ActiveSheet.</code> but it does if you type <code>ActiveWorkbook.</code>

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> And fast as always, Hans!

    (Sometimes I get the feeling you're looking over my shoulder when I'm typing...)

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    In reference to no Intellisense when typing ActiveSheet, the reason why you don't get "Intellisense" for some objects, like ActiveSheet, is because the Application ActiveSheet property returns a generic Object data type; this object may be one of five types of sheets (although a workbook has a Sheets collection, there is no Sheet object) - WorkSheet, Chart, etc. For example, this sub:

    <code>Public Sub TestSheetType()</code>

    <code> Dim obj As Object</code>

    <code> Application.Workbooks.Add</code>
    <code> ActiveWorkbook.Sheets.Add Type:=xlWorksheet, After:=Sheets(Sheets.Count)</code>
    <code> ActiveWorkbook.Sheets.Add Type:=xlExcel4MacroSheet, After:=Sheets(Sheets.Count)</code>
    <code> ActiveWorkbook.Sheets.Add Type:=xlChart, After:=Sheets(Sheets.Count)</code>

    <code> Debug.Print "Sheet Objects:"</code>
    <code> For Each obj In ActiveWorkbook.Sheets</code>
    <code> Debug.Print obj.Index & " " & obj.Name & " (" & GetSheetType(obj.Type) & ")"</code>
    <code> Next obj</code>

    <code> Debug.Print vbCrLf & "Sheet Total: " & ActiveWorkbook.Sheets.Count</code>
    <code> Debug.Print "Worksheet Count: " & ActiveWorkbook.Worksheets.Count</code>
    <code> Debug.Print "Chart Count: " & ActiveWorkbook.Charts.Count</code>
    <code> Debug.Print "Macro Sheet Count: " & ActiveWorkbook.Excel4MacroSheets.Count </code>

    <code> Set obj = Nothing</code>
    <code>End Sub</code>

    <code>Public Function GetSheetType(lSheetType As Long) As String</code>

    <code> Select Case lSheetType</code>
    <code> Case xlChart ' -4109 </code>
    <code> GetSheetType = "Chart"</code>
    <code> Case xlWorksheet ' -4167 </code>
    <code> GetSheetType = "Worksheet"</code>
    <code> Case xlDialogSheet ' -4116 </code>
    <code> GetSheetType = "Dialog Sheet"</code>
    <code> Case xlExcel4MacroSheet ' 3</code>
    <code> GetSheetType = "Excel4 Macro Sheet"</code>
    <code> Case xlExcel4IntlMacroSheet ' 4</code>
    <code> GetSheetType = "Excel4 Intl Macro Sheet"</code>
    <code> Case Else</code>
    <code> GetSheetType = "Unknown"</code>
    <code> End Select</code>
    <code>End Function</code>

    Returns the following results:

    <code>Sheet Objects:</code>
    <code>1 Sheet1 (Worksheet)</code>
    <code>2 Sheet2 (Worksheet)</code>
    <code>3 Chart1 (Excel4 Macro Sheet)</code>
    <code>4 Macro1 (Excel4 Macro Sheet)</code>

    <code>Sheet Total: 4</code>
    <code>Worksheet Count: 2</code>
    <code>Chart Count: 1</code>
    <code>Macro Sheet Count: 1</code>

    Note when you loop thru Sheets collection, a Chart sheet returns a bogus sheet type, but when xlChart specified with Add method, a chart sheet was inserted (although NOT in the position specified by "After" arg, as shown in test results; juggling sequence in which sheets inserted did not change this behavior). Worksheets always returned correct sheet type. Anyway since ActiveSheet can represent one of five different objects, with different properties/methods, that's why there's no "Intellisense" (the same way a generic Object variable will have no Intellisense). The Application ActiveWorkbook property specifically returns a Workbook object - there's no ambiguity, so Intellisense works. Based on these results I would term Excel VBA as "flakey" in addition to "finicky."

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    That's a valid point, but still... If I type

    <code>Workbooks("Book1").Worksheets("Sheet1").</code>

    or

    <code>Workbooks("Book1").Charts("Chart1").</code>

    IntelliSense doesn't kick in either, although I have now specified that I am using a Worksheet or Chart object. There is no ambiguity.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    I would guess the reason why there's no IntelliSense when you type something like <code>ActiveWorkbook.Worksheets.Item(1).</code> (or use the abbreviated syntax, where "Item" is implied), when you look at the Worksheets or Charts Item property in Object Browser, it is defined as follows:

    <code>Property Item(Index) As Object</code>
    <code> read-only</code>
    <code> Member of Excel.Worksheets</code>

    <code>Property Item(Index) As Object</code>
    <code> read-only</code>
    <code> Member of Excel.Charts</code>

    The Item property returns a generic Object, rather than a specific object like Worksheet or Chart - why, who knows, AFAIK there's only one type of Worksheet or Chart sheet. Possibly for some reason related to "backwards-compatability", the reason there's still archaic artifacts in the object model like Macro and Dialog sheets. In comparison, if you examine the Item property for the Windows collection, it returns a specific object type (Window), thus Intellisense is provided when accessing an item in collection, as shown in attached pic.

    <code>Property Item(Index) As Window</code>
    <code> read-only</code>
    <code> Member of Excel.Windows</code>

    Anyway what I usually do is just declare variable specifically As Worksheet, then can use Intellisense. Incidentally if using ActiveSheet, and need to test what type of sheet is active, found VBA TypeName function to be more reliable:

    <code>? TypeName(ActiveSheet)</code>
    <code>Worksheet</code>
    <code>? TypeName(ActiveSheet)</code>
    <code>Chart</code>

    <code>? TypeName(ActiveWorkbook.Sheets(1))</code>
    <code>Worksheet</code>
    <code>? TypeName(ActiveWorkbook.Sheets(4))</code>
    <code> DialogSheet</code>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    Thanks again. Microsoft's decision to define items of the Worksheets and Charts collections as Object make IntelliSense less "intelligent" than it could be.

    > Anyway what I usually do is just declare variable specifically As Worksheet

    Yes, that is what I do too, even when I want to use ActiveSheet.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Zoetermeer, Zuid-Holland, Netherlands
    Posts
    559
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Creating a new doc with VBA (WinXP / Word+Excel 2003 NL)

    Thanks for the clarification! Learned a lot. Again.
    About 95% of my developing is done in Word VBA, and just occasionally in Excel VBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •