Results 1 to 7 of 7

Thread: [RESOLVED] referencing a workbook and table object from a called function or procedure

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Resolved [RESOLVED] referencing a workbook and table object from a called function or procedure

    Hi Forum. Good morning.

    I would like to know how best to reference a workbook and a table object from within a called function or procedure. I have the calling procedure within the worksheet module (named as Libby) and the called function is within the Modules (named as module1).

    What I want to be able to do is update a row on a table held on the worksheet 'Libby' after I create a new workbook via a called function within "module1". Within this called function I execute some commands and it's here that I want to update the table. I know I can pass back the result from the function and I'm doing that already. I just want more control over updating the calling workbook.

    Is it best to store the workbook name and table as public variables so they are visible within the called procedure?

    Just really want to know what is the best way to handle this scenario. Any code samples would help too.

    Many thanks as always.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: referencing a workbook and table object from a called function or procedure

    Is it best to store the workbook name and table as public variables so they are visible within the called procedure?
    it is best to use public (global) level variables as little as possible, the best is to pass variables to the function as parameters, unless there is some reason why global would work better
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: referencing a workbook and table object from a called function or procedure

    Thanks for your reply Westconn1

    If I am in a function and want to output some values to a table then I need to pass the name of the table + worksheet + column heading into the function so that I can reference those in the function. Is that best practise?

  4. #4
    Member
    Join Date
    Mar 2013
    Posts
    35

    Re: referencing a workbook and table object from a called function or procedure

    Hi

    Can you not pass the ThisWorkbook object by reference into your procudure, or am I missing something?

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: referencing a workbook and table object from a called function or procedure

    Hello Yowser

    I do pass the 'Thisworkbook' object by reference to the function but when I get an error when the line runs; 9 - subscript out of range. I setup a test to see if the called function can read a cell value from the calling workbook using the line below.

    [code]
    MsgBox Callingwb.Worksheets("Sheet4").Range("E4")
    [/code/

    'Callingwb' is set as "ByRef Callingwb" in the function header.

    I know the object name is visible within the called procedure because I see it when i use 'callingwb.name'. So something wrong with the structure of my msgbox code?

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jun 2012
    Posts
    90

    Re: referencing a workbook and table object from a called function or procedure

    Ah, it's working now. I replaced 'sheet4' with the actual sheet name and now the values is returned. But isn't it better to use the 'internal' name of the sheet in case somebody remains the sheet at some point? How would I reference the internal name?

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: referencing a workbook and table object from a called function or procedure

    the name you are referring to is the sheet's codename
    it is an object in itself so you would use like
    Code:
    msgbox sheet4.range("e4")
    But isn't it better to use the 'internal' name of the sheet in case somebody remains the sheet at some point?
    generally no, you have no control over the codename of a sheet, it is generated by excel when the sheet is created, sheet2 may have a codename of sheet4, while most of my worksheets are named sheet1 etc, in an ideal world all sheets would have meaningful names, though in programming it is sometimes advantageous to have many items with single name, with numeric identifier

    to access sheet4 in a specific workbook, by codename, i believe, you would need to iterate the sheets by codename to find the correct one
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width