small fuction problem pass arguments

[ina]

hello all,

I have one sub and one function; ma function need to calculate the
range from the activecell + size array.

this is the code;

Sub display_array()
Dim s(3, 3) As String
Dim arraysize As Integer
Dim rng As range

s(1, 1) = "A"
s(2, 1) = "B"
s(3, 1) = "C"

arraysize = 3

rng = selectRange(arraysize)


rng = s

End Sub


Function selectRange(size As Integer) As range
Dim arraysize, j, h As Integer
Dim newcolumn As String
Dim rng As range

arraysize = size

Dim addr As String
addr = ActiveCell.Address
j = ColRef2ColNo(addr)

h = j + arraysize

newcolumn = ColNo2ColRef(h)
range(ActiveCell, newcolumn + CStr(10)).Select

selectRange = rgn


End Function


I have a problem because my function is empty and I do not understand
why. Can someone help me on that?

Ina

 

[Bob Phillips]

You don't supply the sub ColNo2ColRef, but this line

rng = selectRange(arraysize)


should at the least be

Set rng = selectRange(arraysize)

You use

j = ColRef2ColNo(addr)

and

newcolumn = ColNo2ColRef(h)

which are different names, and then

selectRange = rgn

which is an undeclared variable and should be

Set selectRange = rng

You need to start to learn to use Option Explicit at the start of your
modules.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[ina]

Thank you Bob for this explanamtion the colno2colref and colref2colino
are function that transform the column number in a column letter.

Function ColRef2ColNo(ColRef As String) As Integer
ColRef2ColNo = 0
On Error Resume Next
ColRef2ColNo = range(ColRef & "1").column
End Function

Function ColNo2ColRef(ColNo As Integer) As String
If ColNo < 1 Or ColNo > 256 Then
ColNo2ColRef = "#VALUE!"
Exit Function
End If
ColNo2ColRef = Cells(1, ColNo).Address(True, False, xlA1)
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)
End Function


But I have a problem with my code I cannot do

rgn = s

why?

ina

 

[Bob Phillips]

You need to declare the array as 1 based

Dim s(1 To 3, 1 To 3) As String

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)