Hello Everybody,

I have this sub that display an array, I create a function that select
the correct range for my array. i have a problem to pass the argument
to the function

Sub displayArray()
Dim s(3, 3) As String
Dim arraysize, size, i As Integer
Dim rng_adrss, string1 As String

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

'rng_adrss this is the return string from my function
'size is the input of my function

size = 3

Debug.Print size

rng_adrss = selectRange(size)

Debug.Print rng_adrss

range(rng_adrss) = s

End Sub



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

arraysize = size

Debug.Print arraysize
Dim addr As String
addr = ActiveCell.Address


j = ColRef2ColNo(addr) ' return column number
h = j + arraysize
newcolumn = ColNo2ColRef(h) ' return column letter

rowcell = ActiveCell.Row
arraysize = arraysize + rowcell

Set rng = range(ActiveCell, newcolumn & arraysize)
addr1 = rng.Address

selectRange = addr1

End Function

my problem is that my function does not accepted my variable size tell
me that BYREF ARGUMENT TYPE MISTATCH

I do not know why?

Ina

You dim statements are not doing what you think they are doing. Most of your
variables are of type variant. Check out Chip's site for more info...

http://www.cpearson.com/excel/variables.htm

This should fix up your problem...
--
HTH...

Jim Thomlinson


"ina" wrote:

Hello Everybody,

I have this sub that display an array, I create a function that select
the correct range for my array. i have a problem to pass the argument
to the function

Sub displayArray()
Dim s(3, 3) As String
Dim arraysize, size, i As Integer
Dim rng_adrss, string1 As String

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

'rng_adrss this is the return string from my function
'size is the input of my function

size = 3

Debug.Print size

rng_adrss = selectRange(size)

Debug.Print rng_adrss

range(rng_adrss) = s

End Sub



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

arraysize = size

Debug.Print arraysize
Dim addr As String
addr = ActiveCell.Address


j = ColRef2ColNo(addr) ' return column number
h = j + arraysize
newcolumn = ColNo2ColRef(h) ' return column letter

rowcell = ActiveCell.Row
arraysize = arraysize + rowcell

Set rng = range(ActiveCell, newcolumn & arraysize)
addr1 = rng.Address

selectRange = addr1

End Function

my problem is that my function does not accepted my variable size tell
me that BYREF ARGUMENT TYPE MISTATCH

I do not know why?

Ina