[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.

Quote:

---

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

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?

Hi

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

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?

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?

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")

---

Quote:

---

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