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" <roberta.inalbon@gmail.com> wrote in message
news:1145960215.275589.153360@t31g2000cwb.googlegroups.com...
> 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
>

[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 wrote:
> 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" <roberta.inalbon@gmail.com> wrote in message
> news:1145960215.275589.153360@t31g2000cwb.googlegroups.com...
> > 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 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)

"ina" <roberta.inalbon@gmail.com> wrote in message
news:1145967015.718869.71440@v46g2000cwv.googlegroups.com...
> 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 wrote:
> > 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" <roberta.inalbon@gmail.com> wrote in message
> > news:1145960215.275589.153360@t31g2000cwb.googlegroups.com...
> > > 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
> > >
>