Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

998 Chapter 40

Note Cells A2 and A3 in the Client (fm3_chapter40_client.xls) contain

a reference to a currently loaded workbook (no disk address).

The relevant function in the Server is

Function func1( )

func1 = “Server Here”

End Function

All is well if both workbooks are currently loaded in Excel, but if you

load just the Client, you get the following message:

999 Objects and Add-Ins

This notice is produced any time a workbook containing a line (external

reference) to another workbook is opened. If you click Update, the data

will be updated as promised, but the function will not.

Note that the links in cells A2:A3 contain a full reference including

a disk location. If we now open the Server, all will be ﬁ ne because the

references will now contain a disk location.

The moral of this section is, if we want to use functions in another

workbook we have to make sure it is loaded into Excel. The next section

shows one way of doing so automatically.

40.9 References to External Functions in VBA

Referring to functions in an external workbook in VBA is slightly more

complicated than referencing external functions in the worksheet itself.

1000 Chapter 40

As in the previous section, this section uses two separate workbooks.

The ﬁ rst workbook, fm3_chapter40_vba_server.xls, will contain the

externally referred to cells and functions, and from now on shall be

referred to as the Server. The second workbook, fm3_chapter40_vba_

client.xls, will make use of the functions and cells, and from now on shall

be referred to as the Client. Here are screen shots of the ﬁ les in the

original condition:

1001 Objects and Add-Ins

Now that the ground is set, we proceed with the actual work, which,

as promised, will make us go through a few hoops. We are going to create

a situation where a function deﬁ ned in one workbook (the Server) can

be used in VBA in another workbook (the Client). As a side beneﬁ t the

Server will be loaded automatically whenever the Client is used, solving

the problem presented at the end of the preceding section.

40.9.1 Give a Project a Name

The process begins on the Server side. We need to give the VBA

project of the workbook a unique name. Normally all projects are called

VBAProject and this is just ﬁ ne, but since we are going to use the server

project from another project (probably called VBAProject), our server

project needs a proper name. To give it one, open the workbook and

then open the VBA editor. On the toolbar of the VBA editor, select

Tools|VBAProject Properties and change the name; you can add a

description if you like.

1002 Chapter 40

Notice that the project name has changed in the Project Explorer.

40.9.2 Attach a Reference to the Client Workbook

Open the Client workbook. Open the VBA editor. Select

Tools|References.

1003 Objects and Add-Ins

Select Browse.

1004 Chapter 40

Change the ﬁ le type to Microsoft Ofﬁ ce Excel Files, browse to the loca-

tion of the exporting ﬁ le, select it, and select open.

1005 Objects and Add-Ins

Once Open has been clicked, a reference to the ﬁ le is now added to client

workbook. Note that the reference is absolute (full path name) and not

relative to the location of the client.

Click OK to apply the changes and close the references dialogue. Note:

A new references category has been added to the project manager under

the client project, and the Server is now open. Save the client.

1006 Chapter 40

40.9.3 Using a Function in a Reference

Open the Client in Excel, and notice that the Server is automatically

opened in the background. The Client can now use functions in the

Server with impunity, being assured that the Server will be opened auto-

matically and the functions will be available.

Four User Deﬁ ned func-

tions are now available in the Client

—from the Client we get Func1()

and FuncC(), and from the server Func1() and FuncS(). The following,

somewhat contrived, macro demonstrates their use.

5. This situation is unlike the one demonstrated near the end of section 40.8 where only

references to cells in the server were updated automatically when the client was

opened.

6. Actually the functions are available in both the Client and the Server, since both are

currently loaded, but we are only assured that the Client will load the Server and not

the other way around.

1007 Objects and Add-Ins

Note the use of the dot (.) operator to refer to functions in the Server.

Although we can use functions with unique names in the Server without

explicitly referring to the Server, this practice is frowned upon, and we

should always say that a function is not local when referring to it. Here

is the output produced when this macro is run:

Sub ClientServer()

Dim S As String ‘ The message string

‘--- Next line: Title for MsgBox

Const T As String = “Client Server Demo Reporting”

S = “Func1 is “ & Func1 & vbCr ‘ What is Func1

‘ Here?