Return ColorIndex array

**olasa** · 08-01-2008, 07:38 AM

Hello,

I thougt I would 'try' to learn VBA.

My first attempt is to return a simple Array of ColorIndexes.
Could someone please help me with what is wrong.

Ola

Function RangeColor(Range As Range)

Application.Volatile True
Dim cell As Range

For Each cell In Range
    RangeColor = cell.Interior.colorindex
Next

End Function

**dominicb** · 08-01-2008, 07:48 AM

Good afternoon olasa

Congrats on taking the plinge!! It seems you're already into the weeping and gnashing of teeth stage

. The good news is it doesn't get any better ...

If you're trying to return a series of numbers you're going to have to return it as a string. That was the problem with your function - it was returning multiple values, but a function can only return one value. I have made an amendment that shows each ColorIndex value seperated by a comma.

Function RangeColor(Range As Range)
Application.Volatile True
Dim cell As Range
a = ""
For Each cell In Range
a = a & cell.Interior.ColorIndex & ", "
'    RangeColor = cell.Interior.ColorIndex
Next
RangeColor = Left(a, Len(a) - 2)
End Function

Also, when working with functions and ColorIndex you don't really need to use Application.Volatile, as the ColorIndex property doesn't get updated when a cell is changed - only when you force a recalc.

HTH

DominicB

**olasa** · 08-01-2008, 09:48 AM

I had a big laugh I must say. Thanks for the cheers.
I did not think I could stay anonymous for very long so I just had to bite it and come clean.

Thanks, but I want a VBA function that returns a VBA Array like the examples below. Not a text string - if possible.
So when I read "it was returning multiple values" - I sounded as if I was on the right path.

I also read that: "a function can only return one value" ??
Am I missing something or...I had a look at this and this - but I didn't know if it could solve my problem.

And yes I am tossing books and loosing some hair over this.
//Ola

Function MonthNames()
    MonthNames = Array("Jan", "Feb", "Mars")
End Function
-------

Private Function ReturnArray() As Variant

Dim x(3) As Integer 'Declare a Static Integer Array of 4 elements
x(0) = 1
x(1) = 2
x(2) = 3
x(3) = 4
ReturnArray = x 'Pass the array back as a return value

End Function

I might as well attach my feable attemps

**olasa** · 08-01-2008, 02:42 PM

For the record, solved. Questionably pretty, but a start.
It returns either a ColorIndex Row Array or Column Array.

//Ola

Function CellColor(Range As Range)

Dim i As Integer
j = Application.Max(Range.Rows.Count, Range.Columns.Count)
ReDim Cell(1 To j)

Application.Volatile True

For i = 1 To j
    Cell(i) = Range(i).Interior.colorindex
Next

RangeColor = Application.Transpose(Cell)

End Function

**shg** · 08-01-2008, 02:59 PM

You could return an array the same size and shape as the range. And if you're learning VBA, a good habit to get into is to declare all variables, which you can enforce by adding Option Explicit at the top of every module. And I wouldn't choose variable names that are the same as VBA key words.

Function CellColor(r As Range) As XlColorIndex()
    Dim aCI()   As XlColorIndex
    Dim cell    As Excel.Range

    ReDim aCI(r.Row To r.Row + r.Rows.Count - 1, r.Column To r.Column + r.Columns.Count - 1)

    For Each cell In r
        aCI(cell.Row, cell.Column) = cell.Interior.ColorIndex
    Next

    CellColor = aCI
End Function