Solved

Affect Range in a Function and not loose Data

Posted on 2013-05-16
15
320 Views
Last Modified: 2013-05-18
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
0
Comment
Question by:gowflow
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 3

Expert Comment

by:gnazareth
ID: 39172376
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
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 50 total points
ID: 39172438
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
 
LVL 29

Author Comment

by:gowflow
ID: 39172457
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
 
LVL 3

Assisted Solution

by:gnazareth
gnazareth earned 50 total points
ID: 39172469
Try not closing the workbook.
Remove the line:

WB.Close savechanges:=False

Open in new window

0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 39172521
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
 
LVL 29

Author Comment

by:gowflow
ID: 39172609
@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
 
LVL 29

Author Comment

by:gowflow
ID: 39172623
@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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 3

Expert Comment

by:gnazareth
ID: 39172625
MyVariable = IsValidCCFile("c:\test\foo.xlsx")
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39173011
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
 
LVL 45

Accepted Solution

by:
aikimark earned 300 total points
ID: 39173702
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
 
LVL 29

Author Comment

by:gowflow
ID: 39173937
@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
 
LVL 29

Author Comment

by:gowflow
ID: 39173959
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
 
LVL 29

Author Comment

by:gowflow
ID: 39174758
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
 
LVL 45

Expert Comment

by:aikimark
ID: 39175338
@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
 
LVL 29

Author Closing Comment

by:gowflow
ID: 39176786
Tks for the help of everyone. I thought it was obvious but seems a range exists until its connected workbook is closed.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now