Solved

Affect Range in a Function and not loose Data

Posted on 2013-05-16
15
310 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

708 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

16 Experts available now in Live!

Get 1:1 Help Now