VBA newbie question

[peter.thompson]

How do you "execute" a VBA "function". I'm trying to use the following code (found on the net) to check whether a file exists when a workbook is activated. If the file doesn't exist I want to save a copy of the workbook as a given name "Master Template".

Function FileExist(sTestFile As String) As Boolean

sTestFile = "C:\Business Cases\Master Template.xls"
Dim lSize As Long
On Error Resume Next
lSize = -1
lSize = FileLen(sTestFile)
If lSize < -1 Then

ActiveWorkbook.SaveAs Filename:="C:\ROI Business Cases\Master Template.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If

End Function

[Carim]

Hi Peter,

In case of a Function, just type in your function in any cell of your
worksheet ...

HTH
Cheers
Carim

[peter.thompson]

Thanks Carim, my learning is moving up a notch!

Cheers

Peter

[Bob Phillips]

That code does not need to be a function because it does not return a
result, which is the normal practice for a function. Also, I don't think you
should be using it within a worksheet as again it isn't returning a result,
so nothing shows in the cell.

I think you should be calling it from within another macro, probably from
within the Workbook_Open event.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"peter.thompson"
<[email protected]> wrote in
message news:[email protected]...
>
> Thanks Carim, my learning is moving up a notch!
>
> Cheers
>
> Peter
>
>
> --
> peter.thompson
> ------------------------------------------------------------------------
> peter.thompson's Profile:
http://www.excelforum.com/member.php...o&userid=29686
> View this thread: http://www.excelforum.com/showthread...hreadid=501319
>

[peter.thompson]

That is exactly what I have ended up doing, using different code that I found on the net:

Dim fso
Dim file As String
file = "C:\ROI Business Cases\Master Template.xls" ' change to match the file w/Path
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.FileExists(file) Then
ActiveWorkbook.SaveAs Filename:="C:\ROI Business Cases\Master Template.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End If


Cheers and thanks Bob

Peter