Re referencing an object

[rci]

Hi all... a newbie question...

I would like to, in a function, create an object, then do things to it.

ex: Dim SpreadSheet1 As OWC10.Spreadsheet
Set SpreadSheet1 = CreateObject("OWC10.Spreadsheet")

and then play around with SpreadSheet1 as needed.

But what about when I re-enter the function? I don't want to create a new
object... I want to simply reference the object that was created earlier!


What is the proper programming methodology to handle this?

I could move the object creation outside of the function... but would it
then exist within the context of the function? How do I discover, from
within the fucntion, if the object already exists... and then to reference
it? Do I somehow pass the object, or some reference/pointer to the object
to the function is some way?

Thanks for helping a newbie...

MP

 

[Daniel Klann]

Hi,

You declare the Spreadsheet1 in the declarations section of your module.
This will make it available to either the entire project, or just the module
in which it's declared, depending on how you declare it. If you declare it
as private then it will only be available to procedures within that
particular module. If you declare it public it will be available to your
entire project. For more information see the topic 'Understanding Scope and
Visibility' in VBA's help. To test whether an object exists you can use the
'Is Nothing' test. This sample code demonstrates this technique. Notice
that the message box will only display once. To use this code you need to
set a reference to the Microsoft Scripting Runtime.

Option Explicit

Private FSOBJ As Scripting.FileSystemObject

Function CreateAnObject()

If FSOBJ Is Nothing Then
Set FSOBJ = CreateObject("Scripting.FilesystemObject")
MsgBox "An object was created"
End If

End Function


Sub Tester()

CreateAnObject
CreateAnObject

Set FSOBJ = Nothing

End Sub

Hope that helps you,
Dan