FileName/Worksheet Index Number problem 1

[sxschech]

I have used this code for about several months without problem running. For some reason, when I ran it today, it no longer worked. The last time I saved the excel file was in June (filedate 06/12/2006) and at that time the change was not to code only to the data entered into cell A2.

stDate = Workbooks("FormatAP").Worksheets("Sheet1").Range("A2")

I was able to get the code to run by changing the Filename and Sheets to their index numbers, but since this isn't as readable for other users who may run this in the future, I would prefer not to have to change all the code that uses these references. As I have used the "FormatAP" and "Sheet1" syntax in several other places in the module.

    stDate = Workbooks(1).Worksheets(1).Range("A2")

Any thoughts about why the names no longer work and how it could be fixed would be greatly appreciated.

We are using MS-Excel 2003 (11.8012.6568) SP2
And the only other change I can recall to this computer since June was that we needed to uninstall and reinstall DreamWeaverMX 2004 last week, although I wouldn't think that should have an impact on excel vba.

 

[SkipVought]

Hi,

Must be that you no longer have a Workbook, "FormatAP" or a Worksheet, "Sheet1"

Skip,

_[red][/red]

 

[DaleWatson123321]

I'd strongly recommend you start a practice of assigning "range names" to any cells such as in your example.

Internally, Excel keeps track of the location of cells that are assigned a "range name". Therefore, it's a extremely useful when writing VBA - whereby you don't have to be concerned about changes such as inserting rows/columns, or changing the name of worksheets.

The method I use for creating a range name:
1) Highlight the cell
2) Hold down <Ctrl> and hit <F3>
3) Type the name
4) Hit <Enter>


For example, if you assigned a name such as "val_1" (don't enter the quotes), then, all you need for your code is...

stDate = [val_1].value

(BTW, you can assign any name - up to 15 characters)

Hope this helps,

Regards, Dale Watson

 

[sxschech]

Thanks guys.

Skip, The vba code is called from a module within the file called FormatAP.xls (I know the name hasn't changed because it is opened via follow link from Access and I also verified that it is still on the server) and indeed there is a "sheet1" as that is the sheet that holds the "button" that users click on to run the code. Too bad can't show a screen shot of this.

If it helps, I am posting the beginning PART of the code here. What it does is open another excel file and perform extensive formatting to "pretty it up".

Dale, if I use a cell reference how do I also tell it about the file "FormatAP.xls" or is this no longer necessary when using the reference?

    Dim stFG As String
    Dim stDate As String
    Dim stqryName As String
    Dim stTabName As String
    
    stDate = Workbooks("FormatAP").Worksheets("Sheet1").Range("A2")
    For i = 1 To 2
        If i = 1 Then
            stFG = "Trad Undergraduate Applications by "
            stqryName = "qryAPP_F_"
            stTabName = "UG_"
        Else
            stFG = "Graduate Applications by "
            stqryName = "qryAPP_G_"
            stTabName = "G_"
        End If
    
        Sheets(stqryName & "DOMInt_Totals").Select
        Sheets(stqryName & "DOMInt_Totals").Name = stTabName & "DOMInt"
        Call PasteTable
        'Worksheets(stTabName & "DOMInt").Range("B1") = stFG & "Dom/Intl Students " & stDate
        'Range("B1") = stFG & "Dom/Intl Students " & stDate
        ActiveSheet.PageSetup.CenterHeader = stFG & "Dom/Intl Students " & stDate
         
        Sheets(stqryName & "EthGendTotals").Select
        Sheets(stqryName & "EthGendTotals").Name = stTabName & "EthGend"
        Call PasteTable
        'Worksheets(stTabName & "EthGend").Range("B1") = stFG & "Gender/Ethnicity " & stDate
        ActiveSheet.PageSetup.CenterHeader = stFG & "Gender/Ethnicity " & stDate
        Range("B2:B3").EntireRow.Insert
        If i = 1 Then
            Workbooks("FormatAP").Worksheets("Sheet1").Range("A5:Y6").Copy
            ActiveSheet.Paste Destination:=Range("B2:Z3")
        Else
            Workbooks("FormatAP").Worksheets("Sheet1").Range("A9:Z10").Copy
            ActiveSheet.Paste Destination:=Range("B2:AA3")
        End If

....Code Continues

 

[SkipVought]

How about

stDate = Workbooks("FormatAP.xls").Worksheets("Sheet1").Range("A2")

Skip,

_[red][/red]

 

[sxschech]

That did the trick. Thanks so much. I wonder why it worked without it before though.

 

[PHV]

The windows explorer option for hiding known extensions is likely unticked now.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sxschech]

Thank you PHV, we did change the folder options yesterday to apply folder view options to all folders, so perhaps that is what caused the problem. I didn't realize that would affect my code. Thanks for your insight into the issue.

 

[firefytr]

I would highly recommend you stay away from naming ranges. They are error prone and can bloat a workbook beyond any belief.

As for your file extension, you should specify it anyway, it's good habit.

I recommend you use variables in your code for the worksheets and workbooks, such as ...

        Set ws = Sheets(stqryName & "DOMInt_Totals")
        ws.Name = stTabName & "DOMInt"

You can test if a workbook is open by using ...

Function WbOpen(wbName As String) As Boolean
    On Error Resume Next
    WbOpen = Len(Workbooks(wbName).Name)
End Function

.. and set a workbook to a variable such as ...

    Dim wb As Workbook
    Dim strPath As String, strName As String
    strPath = "C:\MyPath\MyFile.xls"
    strName = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
    If WbOpen(strName) Then
        Set wb = Workbooks(strName)
    Else
        Set wb = Workbooks.Open(strPath)
    End If

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

I would strongly disagree with Zack.

I have used Named Ranges with great success for over 10 years in Excel.

I find that they aid in understanding, and simplify worksheet coding.

I incorporate named range defining in my code when I refresh querys, so ranges are always up-to-date.

Skip,

_[red][/red]

 

[firefytr]

I agree they can be beneficial in some cases, but from everything that I've seen, there is the majority of people who use ranged names with gross negligence and it ultimately leads to many more problems under the surface that are not easily found or mitigated. The same goes for using them in VBA, people just generally use them wrong.

So I guess I'll say I don't like named ranges with the disclaimer that they must be used in a good fashion and with the utmost respect in mind. ;-)

And yes, I use them. LOL!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[PHV]

I second Skip on named ranges being very useful with QueryTables (and hidden parameters sheet too).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

There was a lot of interesting comments when Aaron Blood brought this up a while back ...

http://www.vbaexpress.com/forum/showthread.php?p=25053

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21416033.html

http://www.ozgrid.com/forum/showthread.php?t=33449

http://www.mrexcel.com/board2/viewtopic.php?t=144698

http://www.vbaexpress.com/forum/showthread.php?t=3083

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

I most often use column named ranges, generated by Insert/Name/Create - Create names in top row.

Often this is a query table...

    With wsWeeklyData.QueryTables(1)
        .CommandText = sSQL
        .Refresh BackgroundQuery:=False
        Application.DisplayAlerts = False
        .ResultRange.CurrentRegion.CreateNames True, False, False, False
        Application.DisplayAlerts = True
    End With

or use the worksheet_change event.

Skip,

_[red][/red]

 

[GlennUK]

I would also strongly disagree with Zack.

Using Named Ranges helps massively with maintainability in lots of cases, and also make formulae more readable.

There are also the advantages of using Names to do things that could otherwise not be done ... referencing other sheets in Data Validation for example.

The advantages greatly outweigh the disadvantages in my opinion.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.