Function to Return an Array

I am trying to create a function that returns a Boolean array. I'm getting a Type Mismatch error inside the function with the following code:

[VBA]

Dim Neighbors() As Boolean
Neighbors = Neighborhood(Range("e5"))

Function Neighborhood(ByVal cell As Range) As Boolean()

Neighborhood = Array(True, False, True)

End Function

[/VBA]

What is the reason I can't assign the True/False values to the Neighborhood array? It is defined as a Boolean in the Function statement.

Thanks,
clew

An array isn't a boolean, but a Variant().

An array can contain (several) booleans.

Besides: the range is only 1 cell ???

First enter:

[VBA]Function neighborhood() As Variant
Dim ary(1 To 3) As Boolean
ary(1) = True
ary(2) = False
ary(3) = True
neighborhood = Application.Transpose(ary)
End Function[/VBA]

Then select three consecutive cells in a column (say C1 thru C3) and enter:

=neighborhood()

and use CNTRL-SHFT-ENTER rather than just the ENTER key.

A few additional notes / comments to add to GarysStudents' answer

1. The CNTROl_SHIFT-ENTER will add the braces around the cells in the returned array. You should not enter them, even if the formula bar displays them. BTW there are many built in functions that use arrays like that

2. You can't change the formula on the worksheet for one of an array-entered group of cells. Excel will politely tell you "You cannot change part of an array". You have to select and change (or clear) the complete array / range.

3. Control + slash will select the complete array range if one of the cells is selected



Paul

I don't see where cell was used.

See if this helps.
[VBA]Sub Test_Neighbors()
Dim n() As Variant
n() = WorksheetFunction.Transpose(Neighborhood)
Range("E5").Resize(UBound(n)).Value = n()
End Sub

Function Neighborhood() As Variant
Neighborhood = Array(True, False, True)
End Function
[/VBA]

Since the function seems to be invariable it's rather redundant:

[vba]
Sub M_snb()
sn = Array(True, False, True)

Range("E5").Resize(, UBound(sn) + 1) = sn
Range("E10").Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub
[/vba]

Assuming OP wanted to do something with range, a function like this can give boolean results
[VBA]Public Function retboolarray(rng As Range) As Variant
Dim ar() As Variant
Dim r As Range

'initial variable set
ReDim ar(rng.Count - 1)
i = 0

'test a condition for the range
For Each r In rng
ar(i) = CBool(r.Value > 500)
i = i + 1
Next r

'return the function result
retboolarray = Application.Transpose(ar)

End Function
[/VBA]

@shri..

In your case I'd prfer:

[VBA]
Function neighbour(c00, c01)
neighbour = Evaluate(c00.Address & c01)
End Function

Sub tst_neighbour()
Range("A1:A5") = neighbour(Range("D1:D5"), "<200")
End Sub
[/VBA]

Quote:

---

Originally Posted by clew

I am trying to create a function that returns a Boolean array. I'm getting a Type Mismatch error inside the function with the following code:

[vba]

Dim Neighbors() As Boolean
Neighbors = Neighborhood(Range("e5"))

Function Neighborhood(ByVal cell As Range) As Boolean()

Neighborhood = Array(True, False, True)

End Function

[/vba]
What is the reason I can't assign the True/False values to the Neighborhood array? It is defined as a Boolean in the Function statement.

Thanks,
clew

---

[vba]Dim Neighbors As Variant
Neighbors = Neighborhood(Range("e5"))

Function Neighborhood(ByVal cell As Range) As Variant

Neighborhood = Array(True, False, True)

End Function[/vba]