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"

 

[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