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


--
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

Hi Peter,

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

HTH
Cheers
Carim

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

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.21ma1y_1137229201.0676@excelforu m-nospam.com wrote in
message news:peter.thompson.21ma1y_1137229201.0676@excelfo rum-nospam.com...

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

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


--
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