Results 1 to 5 of 5

20050512, 11:53 #1
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Return function to range (Excel 2000)
Hi all,
I don' t know if this can be done, what I would like to do is call the function getpressid1 and return that element of the array and return it to the activecell, then offset the cell. I would like to run in a form, by clicking a button.
Thanks,
Darryl.
Public Function getPRESSID1() As String()
Dim x() As String
Dim y As Integer
ReDim Preserve x(y)
x(y) = "A PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "B PRESS "
y = y + 1
ReDim Preserve x(y)
x(y) = "C PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "D PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "E PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "F PRESS"
y = y + 1
getPRESSID1 = x
Erase x
End Function
Public Sub Cellinput()
Dim x() As String
Dim y As Integer
Do Until y = 6
ActiveCell.Value = getPRESSID1
ActiveCell.Offset(1, 0).Value = y
y = y + 1
Loop
End Sub

20050512, 14:35 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Return function to range (Excel 2000)
I'm sorry, I don't understand. What is "that element" in "call the function getpressid1 and return that element of the array"? Could you try to explain what you are trying to accomplish?

20050512, 14:43 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Return function to range (Excel 2000)
I don't quite understand either what you want. Does this do it?
Option Explicit
Public Function getPRESSID1() as variant
'Must be variant if you plan on making it an array
Dim x() As String
Dim y As Integer
ReDim Preserve x(y)
x(y) = "A PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "B PRESS "
y = y + 1
ReDim Preserve x(y)
x(y) = "C PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "D PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "E PRESS"
y = y + 1
ReDim Preserve x(y)
x(y) = "F PRESS"
y = y + 1
getPRESSID1 = x
Erase x
End Function
Public Sub Cellinput()
Dim x
Dim y As Integer
'Get the array of items and put into a variable
x = getPRESSID1
Do Until y = 6
'read the variable with a particular value
'Put into cells based on the active cell
ActiveCell.Offset(y, 0).Value = x(y)
ActiveCell.Offset(y, 1).Value = y
y = y + 1
Loop
End Sub
Steve

20050512, 14:57 #4
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: Return function to range (Excel 2000)
Hans,
What I am trying to do is return the array into a range of cells, "A Press" would be the first element? I'm not sure if my terminology is correct. the second would be B PRess and so on.... basically count down each value of the function, and place it into a cell. My function getpressid1 is what I call an array, maybe there is another way to define an array, basically if I call this function into a listbox on a form listbox=getpressid1, I get all of the values of the string function. So what I was trying to do is call the same function and place those valuees into a cell.
Can it be done like this? Or am I looking at this wrong?
Thanks,
Darryl

20050512, 15:12 #5
 Join Date
 May 2004
 Location
 Toronto, Ontario, Canada
 Posts
 425
 Thanks
 5
 Thanked 0 Times in 0 Posts
Re: Return function to range (Excel 2000)
Thanks Steve...and Hans.
That's exactly what I was trying to do.
Darryl.