Calling worksheet functions from within a module

[Daz]

hi all.

I'm wondering how I can call a 'public' worksheet function from within
a module, or even from a seperate worksheet.

Say I have a function in a worksheet that sets the background colour of
range of cells to clear, and I have a button on a seperate worksheet,
and on the buttons onClick event I want it to run that function.

Help appreciated.

Darragh

 

[Guest]

Hi Daz,
This is simple. However, it is not the recommended practice. The usual
practice is to place all the common functions in a module.

First create a function shown below in Sheet1

Public Function abc(ByRef ws As Worksheet)
ws.Range("A1:A10").Interior.ColorIndex = 54
End Function

Next create the following function in Sheet2
Public Function Def()
Sheet1.abc Worksheets(3)
End Function

If you call Def() through some method, it will in turn call the Abc()
function in Sheet1. The trick is to use the name of the programmatic name of
the Sheet. Infact once you type the Sheet1. the "abc" will be shown
automatically by the intellisense.

You can put the same function Def() in a module and the effect will be the
same.