On 10 jun, 09:36, "Norman Jones"
wrote:
Hi V,

Barb's suggestion works for me, provided that,
as suggested, you confirm the formula with the
key combination

CTRL-SHIFT-ENTER.

rather than Enter.

For your purposes. however, I think that you
should change Barb's +3 to +1.

To utlise your function, try the following
adaptation:

'=============
Function b(r As Range) As Variant
Dim i As Long, j As Long
Dim arr As Variant
arr = r.Value
For i = 1 To UBound(arr, 1)
For j = 1 To UBound(arr, 2)
arr(i, j) = arr(i, j) + 1
Next j
Next i
b = arr
End Function
'<<=============

---
Regards,
Norman

"vsoler" wrote in message

ps.com...



On 10 jun, 01:34, Barb Reinhardt
wrote:
Why not select B1:B3 and enter the following

= (select A1:A3) + 3

Commit with CTRL SHIFT ENTER.

"vsoler" wrote:
I am a beginner in programming VBA

I want to write a function that returns an array where each element is
increased by 1.

However, something is not working. Could you please help me?

Function b(r As Range)
Dim i As Integer, j As Integer
b = r
For i = 1 To UBound(b, 1)
For j = 1 To UBound(b, 2)
b(i, j) = b(i, j) + 1
Next j
Next i
End Function

For example,

............A..........B
1.........1...........2
2.........2...........3
3.........3...........4

In this case, B1:B3 contains the formula =b(A1:a3) entered with
Ctrl-Shift-Enter

Thank you- Ocultar texto de la cita -

- Mostrar texto de la cita -

Barb,

The solution you propose does not work in my Excel 2003. I get a value
of 3 in all my 3 cells.

I'd rather have a function, because I need to preserve the original
values and put the result of the calculation just side by side.

Can anybody help?- Ocultar texto de la cita -

- Mostrar texto de la cita -

Norman,

It works fine, many thanks.

However, I cannot still understand why I have to use an intermediate
variant array "arr" instead of using directly the function name "b",
since both of them are variant. Where is the difference between the
two?

Thanks again