Archive of Mr Excel Message Board


Back to Errors in Excel archive index
Back to archive home

Compiler Error: Expected Array

Posted by Edward C. on April 03, 2001 2:15 PM
Hi,
I get the error message:

Compiler Error: Expected Array

when I run a macro I built. I believe it is not happy with the way I declared my array, but I don't know whats wrong because I took this example straight from the O'Reilly book "Writing Excel Macros" by Steven Roman. If anyone sees something wrong or has a suggestion I would greatly appreciate any feedback you can give me.
Thanks
Edward
---------------------------------------
Option Explicit
Dim cChartSheets As Integer
Dim sChartSheetNames As String
---------------------------------------
Public Sub UserForm_Initialize()
Dim ws As Object ' Worksheet
ReDim sChartSheetNames(1 To 10)

lstChartSheets.Clear
cChartSheets = 0
For Each ws In ActiveWorkbook.Charts
cChartSheets = cChartSheets + 1

'Redimension arrays if necessary
If UBound(sChartSheetNames) < cChartSheets Then
ReDim Preserve sChartSheetNames(1 To cChartSheets + 5)
End If

' Save name of chart sheet
sChartSheetNames(cChartSheets) = ws.Name

' Add chart sheet name to list box
lstChartSheets.AddItem sChartSheetNames(cChartSheets)
Next
End Sub



Re: Compiler Error: Expected Array

Posted by Dave Hawley on April 03, 2001 2:27 PM
lstChartSheets.Clear cChartSheets = 0 For Each ws In ActiveWorkbook.Charts cChartSheets = cChartSheets + 1 'Redimension arrays if necessary If UBound(sChartSheetNames) < cChartSheets Then ReDim Preserve sChartSheetNames(1 To cChartSheets + 5) End If ' Save name of chart sheet sChartSheetNames(cChartSheets) = ws.Name ' Add chart sheet name to list box lstChartSheets.AddItem sChartSheetNames(cChartSheets) Next

Hi Edward

Having a quick look I would think the code you have needs to be used in conjuction with another macro. Couple of reasons:

1. ReDim sChartSheetNames(1 To 10)

suggests that this array has been declared someplace else.

2. lstChartSheets.Clear
must have been dimensioned elsewhere as an Object.

Dave


OzGrid Business Applications


Re: Compiler Error: Expected Array

Posted by Edward C. on April 04, 2001 6:24 AM
Hi Dave,
Yes, I have that. If you notice in the first part, above the dotted line in the declaration section I have the orginal dim statement for sChartSheetNames. As to the other, I lstChartSheet is the name of a list box in my dialog which this code is connected to.
Thanks for you comments. I hope this helps. Please let me know if you have any further suggestions.
Thanks
Edward


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.