Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Statement and Return Value
Hi,
I just created a function in a personal macro. I'm trying to use the Call statement to call my function within my Sub but by doing this, I loose the return value of the function. I'm looking for a way to use the return value of the function. Is there an other way to call my function? A way I can preserve the return value? Here is my code : Function SelectRangeToChange(ToChange As Range, X As Range, Value As String) Dim Selection As Range, Cell As Range Set Selection = Intersect(X, ActiveSheet.UsedRange) For Each Cell In Selection If (Cell.Value) = "Value" Then If ToChange Is Nothing Then Set ToChange = Cell Else: Set ToChange = Union(ToChange, Cell) End If End If Next Cell End Function Sub SarkFundFormat() Cells.Select Selection.Sort Key1:=Range("C2"), Header:=xlYes Dim ToDelete As Range, CashSelection As Range, Cash As String Call SelectRangeToChange(ToDelete, Range("C:C"), Cash) - here is my problem. I'm trying to refer to the ToDelete range but I can't ToDelete.EntireRow.Copy Sheets("Cash & FX").Paste ToDelete.EntireRow.Delete End Sub Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Statement and Return Value
A function has to return a value but you never set the retrun value
Function SelectRangeToChange(ToChange As Range, X As Range, Value As String) As Range '??? Dim Selection As Range, Cell As Range 'don't use selection reserved word Set Selection = Intersect(X, ActiveSheet.UsedRange) For Each Cell In Selection If (Cell.Value) = "Value" Then If ToChange Is Nothing Then Set ToChange = Cell Else: Set ToChange = Union(ToChange, Cell) End If End If Next Cell set SelectRangeToChange = ToChange '??? End Function sub Test dim rng as range set rng = SelectRangeToChange(...) end sub Note: in a function if you do not specify the return type then the function returns a variant which is not too efficient... -- HTH... Jim Thomlinson " wrote: Hi, I just created a function in a personal macro. I'm trying to use the Call statement to call my function within my Sub but by doing this, I loose the return value of the function. I'm looking for a way to use the return value of the function. Is there an other way to call my function? A way I can preserve the return value? Here is my code : Function SelectRangeToChange(ToChange As Range, X As Range, Value As String) Dim Selection As Range, Cell As Range Set Selection = Intersect(X, ActiveSheet.UsedRange) For Each Cell In Selection If (Cell.Value) = "Value" Then If ToChange Is Nothing Then Set ToChange = Cell Else: Set ToChange = Union(ToChange, Cell) End If End If Next Cell End Function Sub SarkFundFormat() Cells.Select Selection.Sort Key1:=Range("C2"), Header:=xlYes Dim ToDelete As Range, CashSelection As Range, Cash As String Call SelectRangeToChange(ToDelete, Range("C:C"), Cash) - here is my problem. I'm trying to refer to the ToDelete range but I can't ToDelete.EntireRow.Copy Sheets("Cash & FX").Paste ToDelete.EntireRow.Delete End Sub Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Statement and Return Value
Thanks,
Thanks seems to set rng to the return value of my function but when I try to use the rng range to do something like : rng.select It's not working. So I don't know if it's really sets as a range. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call Statement and Return Value
selects are a bit of a beast to deal with. I avoid them like the plague. You
can not select a range that is not on the active sheet... for example dim rngThis as Range dim rngThat as Range set rngThis = sheets("This").Range("A1") set rngThat = sheets("That").Range("A1") Sheets("This").select rngThis.select 'This is good rngThat.select 'This will generate a run time error... -- HTH... Jim Thomlinson " wrote: Thanks, Thanks seems to set rng to the return value of my function but when I try to use the rng range to do something like : rng.select It's not working. So I don't know if it's really sets as a range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a IF statement to return a value | Excel Worksheet Functions | |||
using an if then statement call a caption box to a cell | Excel Programming | |||
using an if then statement call a caption box to a cell | Excel Programming | |||
using an if then statement call a caption box to a cell | Excel Programming | |||
Can you call a macro as in an IF statement | Excel Programming |