We help IT Professionals succeed at work.

Excel 2003 Scope Problem

TigerMan
TigerMan asked
on
Hi,
I have declared "Dim ArtsArr" at the top of a module.
The attached code works fine and populates ArtsArr successfully.  I have Watched it and note that the variable ArtsArr goes out of context immediately on End Sub.  This is a public variable so that should not happen.  Why?

When I run the following code immediately after, I get a Type Mismatch on the For Counter = line.
That is because ArtsArr is now out of context, and therefore UBound(ArtsArr,1) is a null value.

Sub Add2012RCItoArtsArr(ArtsArr)
' calculate the RCI for each FoR in each article, and add into Cols 12, 15, 18 in ArtsArr
For Counter = 1 To UBound(ArtsArr, 1)
Do Stuff
Next Counter
End Sub


Excel insanity yet again ...

Anyone see the problem here?
Sub LoadArticlesFromOriginalSource()

Dim VirtualBook As Workbook, FilePointer As String
FilePointer = ThisWorkbook.Path & "\list-of-jnls.xls"
If Dir(FilePointer) <> "" Then
    Set VirtualBook = Workbooks.Open(FilePointer)
    NumArts = Range("A1").CurrentRegion.Rows.Count - 1
    NumCols = Range("A1").CurrentRegion.Columns.Count
    Dim ArtsArr()
    ArtsArr = Range("A2").Resize(Range("A1").CurrentRegion.Rows.Count - 1, _
                                                Range("A1").CurrentRegion.Columns.Count)
    VirtualBook.Close SaveChanges:=False
Else
    MsgBox "File not found”
End If
End Sub

Open in new window

Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2012
Commented:
try using

Options Explicit

At the top of your code in all modules (just good practice, IMO).  Then, do a Compile Debug to see if there are spelling issues with your variables - you may have miss-typed the variable name - that's why it shows up and drops off.  

However, the problem is probably you need to define it as a PUBLIC variable:

Public ArtsArr as whatever 'don't use Dim as it won't have scope at the end of a sub.

Be advised, there are certain conditions that will STOMP on public variables after the end of the life of an existing codeset.  Let me see if I can find a link on that

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:
Here are a couple links on scoping:
http://www.ozgrid.com/VBA/variable-scope-lifetime.htm
Notice, the comment at the bottom that the life of a global/public variable will end if the END statement is used, the workbook with your project is closed.

There are a couple other instances, but I can't recall nor pull them up from my knowledgebase (lost my keywords in the brain!) but I recall a dissertation I did in a solution on this suggesting that perhaps the BEST (100%) way to ensure the "everlasting" life of a global variable was to store their values in a Name or in a workbook.

However, for most purposes, Public will work just fine.

Dave

Hi
I looked at your sub routine "Sub LoadArticlesFromOriginalSource()". In this sub routine, you have again defined ArtsArr(), say "Dim ArtsArr()" on line no. 9. Remove this line as you have already declared a variable on the top of the module. And try again.


Author

Commented:
Sorry I have been called away to other work.  I will attend to these on Monday.
Dave, you seem to be on the right track with the scoping thing ... I do have Option Explicit.  Are you saying that if I declare a public ArtsArr and then populate ArtsArr in a Sub with an End, that at that point the array loses its values?
V, sorry, but I have to LOL ... that has caught me so many times you would have thought I had learned my lesson by now.  Thanks for that tip ...
I think between you both I will fix this problem ... but Monday it has to be.
Most Valuable Expert 2012
Top Expert 2012

Commented:
if you declare outside the scope of all your subs - at the top of your module:

PUBLIC ArtsArr as whatever 'The life of the variable should be what you desire.

Dim ArtsArr as whatever 'The life WON'T be what you desire
--------------------
VipulKadia may have found your immediate problem, however, as you do have it declared in the sub, it appears :)

Dave

Author

Commented:
Dave, another couple of questions so I have everything for Monday ...
1: What do you mean by "Then, do a Compile Debug"
2: In "Dim ArtsArr as whatever 'The life WON'T be what you desire" it is not enough to say if this then it won't that ... can you expand on this please?
Most Valuable Expert 2012
Top Expert 2012

Commented:
If you use the command at the top of every module:

Options Explicit

Then all variables have to be declared.  When you hit the menu Debug->Compile in the VBE code editor/explorer,
Excel will flag any undeclared variables, flag duplicate declararions and routines, do syntax checking, etc.

If you want a variable to be truly global, maintain its value even when code is not running, declare it

Public x as long ' or whatever.  This variable has scope for the entire project and extended life till an End stmt or wkbk close

Don't declare it:

Dim x as long ' or whatever. This variable has scope for the module, but life only while code is running

See links I posted on the subject for more details, as needed


Does this help?  Sorry- on blackberry

Dave

Author

Commented:
OK, the problem has been resolved by attending to a combination of Public and internally re-declaring a variable.  Thanks.
I am happy to just award the points and answer etc ... but above the idea of storing variable contents in a workbook was suggested.
In some cases I do actually need to do this.  BUT
When i wish to access those variables I wish to avoid actually opening the workbook as it takes too much time.  If you look at the code below you will see a working example that does open the workbook.
Is there a way to extract the necessary data without opening, getting, and closing?
If a solution to that is posted here, I will increase the points.
Please let me know ...
Sub LoadArticlesIntoArtsArr()
' load all articles from list-of-jnls.xls into ArtsArr n recs by 20 columns

Dim VirtualBook As Workbook, FilePointer As String 'declare a workbook
FilePointer = ThisWorkbook.Path & "\list-of-jnls.xls" ' set the path and filename
If Dir(FilePointer) <> "" Then      ' test if blank
    Set VirtualBook = Workbooks.Open(FilePointer)       ' open specified file
    NumArts = Range("A1").CurrentRegion.Rows.Count - 1  ' get number of articles in the list
    NumCols = Range("A1").CurrentRegion.Columns.Count
    ArtsArr = Range("A2").Resize(Range("A1").CurrentRegion.Rows.Count - 1, _
                                                Range("A1").CurrentRegion.Columns.Count)
    VirtualBook.Close SaveChanges:=False
Else
    MsgBox "File not found.  Obtain 'list-of-jnls' and place in same folder as master file'"
    MsgBox "This process will end immeidately.  Re-run with correct data file present"
    Exit Sub
End If
End Sub

Open in new window

Most Valuable Expert 2012
Top Expert 2012

Commented:
You can query a closed workbook - read or write, using ADO.  Here's a quick link on the subject and there's many examples if you google: http://support.microsoft.com/kb/278973

If you go this route and get stuck, feel free to post a question for help.

Dave
Most Valuable Expert 2012
Top Expert 2012

Commented:

Author

Commented:
Does it have to be ADO?  I have disparate non-literate computer users with different platforms in different parts of the world.  A straight code link would be better ... sure I have seen it somewhere back in the dark ages, but cannot find where now.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Writing to excel w file closed is not a very normal operation, without ADO I'm at a loss to what you'd do.  Ask another question that focuses on this issue as this issue is now well out of scope of your original question

Dave

Author

Commented:
Hi Dave,

I am not writing to a closed book, just getting the data from a closed book.

I understand the new question model as that keeps knowledge banks in better shape.

How should I share points 50:50?
I guess you both need to agree?
Hi TigerMan,
You can get the help of How to assign points to the solution of your question from FAQs.

Following is the link.
http://www.experts-exchange.com/help.jsp#hs=26&hi=366

See "How do I close a question?" --> Point-2.
Most Valuable Expert 2012
Top Expert 2012

Commented:
Shared points sounds fine.

The reason I suggested another question is two-fold:  one - for the knowledgebase of course!, but two - my primary reason - was to get others who might look at a fresh question - to come in and provide expert input.  Sometimes that doesn't happen very often when the question already has a longer thread, though I sometimes look at older ones of this type, it might take a while for someone that has the answer - and who went though the throes of just finding the question, to jump in...

FYI -- if you're just reading from the workbook, its a bit simpler.  Go ahead and post if this answer is not helpful:

Either LINK to the closed workbook all the cells that you're wanting to access, or try MOREFUNC utilities - use the INDIRECT.EXT function - this is built to read closed workbooks.

INDIRECT - is kind of like linking to another workbook - where you specify the range address of what you're looking for in a string, then INDIRECT(that address) would return the value you want.  See INDIRECT function in Excel help.  INDIRECT.EXT allows you to do this with closed workbook.

Here's the link to the MOREFUNC utilities that has that functionality:http://xcell05.free.fr/morefunc/english/
-----------------

So, with either of these approaches, you'll be making a range copy of the spreadsheet in question, that is closed.  Linking would be to that exact file (where if the file were to change, you'd have to copy/paste it to the same folder with same name, or your links would not be fruitful).  Using INDIRECT.EXT would allow you to specify the filename (which could change from time to time) and then the addresses that you need.

If its just a question of getting some configuration settings or a few key parameters, either approach might be the ticket.

Dave

Author

Commented:
All good then
I knew HOW to distribute points; I was asking if you guys were happy 50:50
I will look at non-ADO solutions to that problem elsewhere
thanks!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.