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
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
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
