?
Solved

Affect Range in a Function and not loose Data

Posted on 2013-05-16
15
Medium Priority
?
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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 31

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Assisted Solution

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

WB.Close savechanges:=False

Open in new window

0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 400 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 31

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 31

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
 
LVL 3

Expert Comment

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

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 46

Accepted Solution

by:
aikimark earned 1200 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 31

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 31

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 31

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 46

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 31

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

752 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