Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
small fuction problem pass arguments
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
small fuction problem pass arguments
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" wrote in message oups.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
small fuction problem pass arguments
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" wrote in message oups.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
small fuction problem pass arguments
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" wrote in message ups.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" wrote in message oups.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to pass variables as arguments of a function | Excel Worksheet Functions | |||
Can I pass arguments to my formatted empty spreadsheet file? | Excel Discussion (Misc queries) | |||
How to pass arguments to vlookup in VBA | Excel Programming | |||
Run/execute VBS and pass arguments | Excel Programming | |||
How to pass arguments from ThisWorkbook to a UserForm | Excel Programming |