Posts by Mackeral

    This old code doesn't work with the "Names Manager" any more


    TS = Names("Name_to_Lookup").Value

    Now what I get is the "refersTo" information.

    How do you extract the "Value" portion in Excel 2016?

    At this point I have spent so much time converting code that I wish I'd never switched from 2007. 2016 is so much slower to start. Don't switch!

    This old code doesn't work any more



    doesn't work in 2016 any more. (Why would Microsoft change something like this?

    Now what I get is the "refersTo" information.

    How do you extract the "Value" portion?

    Re: vba how to access a different workbook

    I've discovered that the problem lies somewhere else in my code with the SHEET specification. So please ignore this thread.

    I would delete it myself but I can' figure out how. It says "To edit or delete your posts, click the [Blocked Image:] Edit Post button by the particular post. If your post was the first in the thread, then deleting it may remove the entire thread", but I don't see any way to actually delete it when I do that. If someone reads this who know how to mark or delete posts, could you let me know how.


    Re: vba how to access a different workbook

    Since I put a "Watch" on WKBK and found in it's Watch Display the sheets, including one test one that I put there explicitly for that purpose, that I expected, I'm assuming that Workbook_Get returned a valid workbook. I've also tried the code
    Set SHEET = Workbooks(Book_Name).Sheets(Sheet_Name)

    which produced the same error message as previously.


    Here is my code

    Dim WKBK as Workbook
             Dim SHEET As Worksheet
             Set WKBK = Workbook_Get(Path, FileName)
             WkBk_Name = WKBK.Name    
             Set SHEET = WKBK.Worksheets(FileName)  <-- Error 9, "subscript out of range"

    I can put a "Watch" on WKBK. It shows the sheets I expect to find, so obviously (at least to me) WKBK has been initialized. However I get an error on the "Set SHEET" statement. "FileName" is the name of both the Workbook and it's Worksheet that I want. And if you look at WKBK, the sheet is there. Any idea what's going on?

    And as a second question, does the Error Number have any useful meaning?

    Thanks for any help


    Keywords: workbooks vba sheet access