Affect Range in a Function and not loose Data

Hello

I am stumbling on something obvious but for the ... of me I cannot find it so here it is:

I enter the below Function and need the result as Range but what happens is when the instruction WB.close hits my variable is wiped off !!! is there anyway to keep my variable from not being wiped off even when the WB is closed ???

Function IsValidCCFile(FileName As String) As Range
On Error GoTo ErrRoutine

Dim WB As Workbook
Dim WS As Worksheet
Dim MaxRow As Long, I As Long

Application.DisplayAlerts = False


Set WB = Workbooks.Open(FileName)
Set WS = WB.ActiveSheet

If WS.Range("P1") = "" Then
    Set IsValidCCFile = Nothing
Else
    Set IsValidCCFile = WS.UsedRange
End If

ErrRoutineEnd:

WB.Close savechanges:=False
Set WS = Nothing
Set WB = Nothing

Application.DisplayAlerts = True

Exit Function

ErrRoutine:
MsgBox (Error(Err))
GoTo ErrRoutineEnd


End Function

Open in new window


Tks for enlighting me !!!
gowflow
LVL 31
gowflowAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aikimarkConnect With a Mentor Commented:
1. the name of your function is misleading
IsValidCCFile() should return a boolean value, not a range value.

2. In such cases, have the calling/invoking code pass a workbook object into the function as a parameter (ByRef).  Your function will instantiate the parameter variable.
Note: It will be the responsibility of the invoking code to close and dereference the workbook object after finishing with the .Activesheet.UsedRange data.
0
 
gnazarethCommented:
One way is to pass a string of the range instead of the actual range.
Instead of

Set IsValidCCFile = WS.UsedRange

Open in new window


try

Set IsValidCCFile = WS.UsedRange.Address

Open in new window

0
 
andrewssd3Connect With a Mentor Commented:
I agree with gnazareth - you'll have to return as something that will persist - a Range will only ever be valid while its owning workbook is open in Excel.   A string would work - would suggest setting the External parameter of Address to true, so it includes the workbook and worksheet names - it's easier to recover it later from that using the Range method.  Also you would lose the Set if you changed the return type of the function to String:
IsValidCCFile = Address(true,true,xlA1,true)

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
gowflowAuthor Commented:
yes but the address wont give me the contents of the cells !!! I need a range to be returned with its vales so I can iterate thru the whole range and pick what I need.

The idea is open a workbook take a sanpsot of it in a variable then do what you want with it when it is closed !!
gowflow
0
 
gnazarethConnect With a Mentor Commented:
Try not closing the workbook.
Remove the line:

WB.Close savechanges:=False

Open in new window

0
 
Patrick MatthewsConnect With a Mentor Commented:
Then instead of passing back a Range object, pass back an array of values:



Function IsValidCCFile(FileName As String) As Variant
On Error GoTo ErrRoutine

Dim WB As Workbook
Dim WS As Worksheet
Dim MaxRow As Long, I As Long
Dim arr As Variant

Application.DisplayAlerts = False


Set WB = Workbooks.Open(FileName)
Set WS = WB.ActiveSheet

If WS.Range("P1") = "" Then
    arr = ""
Else
    arr = WS.UsedRange.Value
End If

IsValidCCFile = arr

ErrRoutineEnd:

WB.Close savechanges:=False
Set WS = Nothing
Set WB = Nothing

Application.DisplayAlerts = True

Exit Function

ErrRoutine:
MsgBox (Error(Err))
GoTo ErrRoutineEnd

End Function

Open in new window


That returns either a zero length string, or a 2-d array (1 to r, 1 to c) in which r is the number of rows in the original range, and c the number of columns.


Now, when you call that function:

Dim MyVariable As Variant

MyVariable = Function IsValidCCFile("c:\test\foo.xlsx")

If IsArray(MyVariable) Then
    ' you have data
Else
    ' you don't
End If

Open in new window

0
 
gowflowAuthor Commented:
@mahewspatrick
thought so of doing it in an array, trying to replicate your suggestion but not sure you got a syntax error or something at this line

I get a red line on
MyVariable = Function IsValidCCFile("c:\test\foo.xlsx")

and more I need to get a range coz I need to extract data from specific columns like P, Z, AA, DZ, HC etc... more connvenient to do something like
range("DZ" & RowNumber) ... then try to convert the column number of DZ to find it in hte array !!!

I need to get a range back !!! is it mission impossible ????
gowflow
0
 
gowflowAuthor Commented:
@gnazareth
If you do this you end up with a workbook that is opened in the background !!! not good ! especially that I loop thru 1000 record that each need to open a file and extract a range then do some manipulation ... can't leave all these opened till.... we close the main workbook !
gowflow
0
 
gnazarethCommented:
MyVariable = IsValidCCFile("c:\test\foo.xlsx")
0
 
Patrick MatthewsCommented:
I get a red line on
MyVariable = Function IsValidCCFile("c:\test\foo.xlsx")

As gnazareth notes in http:#a39172625, that should be:

MyVariable = IsValidCCFile("c:\test\foo.xlsx")

Open in new window


As noted above, once you close the workbook that range comes from, your range object is destroyed.  So, if you must have a range object, then you must keep that workbook open for as long as you need the range object.

However, if what you really need are the values in that range, then I maintain that my array suggestion is the way to go.
0
 
gowflowAuthor Commented:
@matthewspatrick
Thanks for your comments as well noted.

@aikimark
1) I am not sure the name has to do anything with the value returned as my original post
Function IsValidCCFile(FileName As String) As Range
clearly returns a range.

2) I like your proposal !!! if I read you well I should open the wb prior to calling the function and when back with the Range and finish processing then I should close the wb.

I will give it a try and revert !!!
gowflow
0
 
gowflowAuthor Commented:
To go back to my main subject of concern.

From the comments I already got in this question do I conclude that 'THERE IS NO WAY' to affect a range to a variable and still have this variable keep all its content after the concerned workbook has been closed ????

What is so particular to a range that makes it diffrent from all other declared variables that makes it does not keep its value after WB is closed ???

Is there an other way around this can we declare a variable in a diffrent type to get around this problem ???

As the solution proposed by akimark indeed would work but in fact would make the original sub more crouded as this would entail all of:
1) need to declare variables
2) Open WB
3) Assign WS
.... do manipulations
4) Close WB
5) Clean variables

Which was the main reason why I put all this into a function ... now when I remed out the WB.close I got my range to retain its content when exiting the Function however In my task I had many instances of Excel beeing created and not closed which was surely not convenient.

Appreciate if we can really stir all this up to get to the bottom of this issue.
gowflow
0
 
gowflowAuthor Commented:
Ok here are the results:

I have gone by the suggestion of aikimark as to opening the wb in my main Sub i/o the Function and indeed it is simple and straight to the point and solved my issue with no sweat.

I am still intrested to hear about my previous comment on possibility to affect a range to a variable and keep all the data after the Owning Workbook has been closed.

Rgds/gowflow
0
 
aikimarkCommented:
@gowflow

The name of your function, IsValidCCFile() , IMPLIES that it returns a boolean value.

=============
Think about inheritance.  A Worksheet can not exist independent of its Workbook.  A range variable can not exist independent of its worksheet.  Part of the clean-up (garbage collection) process prevents such orphans.
0
 
gowflowAuthor Commented:
Tks for the help of everyone. I thought it was obvious but seems a range exists until its connected workbook is closed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.