special sheets? 3

[drewdaman]

Hi,
I am not very good with vb, am a c++ person.

In excel, when you write vba macros, is there any special significance for the "This workbook" sheet? from teh name, i assumed that it is a good place to put functions that you need in many sheets... i had a function (which works fine) copied and pasted (ie all copies identical) in the vba sheets 1-3. i thought it might be a good idea to "factor this code out" into the "This workbook" thing.. somehow when i do that, the code works for one of the sheets, but not the others.. i was VERY careful to change all cell references to fully qualified references (ie with the associated sheets- eg. sheet1!A3- which are parameters passed to the functions)..

am i wrong about what the "This workbook" sheet is about?

thanks!

 

[xlbo]

ThisWorkbook refers to code for the workbook only - events and things like that

If you have written a UDF (User Defined Function) in VBA, to be able to call it from any worksheet you must put in in a standard module (those which are generated by using Insert>Module from the VBE menubar)

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Hi,

the This Workbook 'Sheet', is in fact your Workbook Object as are the others your Worksheet Objects.

You might want to write your code in Modules (in the VBE right click --> Insert --> Module) and leave the Objects for specific Event coding.

Cheers,

Roel

ps you can reference the associated sheets with the names listed for them in the VBE (i.e. Sheet1.Cells(1,1))


Cheers,

Roel

 

[drewdaman]

oh ok..

so if i want to have a function that can be called in any sheet i have to insert a new module? how can i call the function once i write it? something like:

call module1.function(param)

or is there anything else i need to use?

thanks for the replies!

 

[PHV]

someVar = functionName(someParam)

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

 

[drewdaman]

brilliant!

thank you very much. copying and pasting a single function into three separate sheets really made me feel stupid!

 

[xlbo]

in the worksheet

=FunctionName()


Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question