PDA

View Full Version : Turn off custom functions



djchou
04-18-2008, 01:51 PM
Help!

My custom functions are running whenever I open a new workbook.

Typically, this would be ok, but I have a macro opening multiple workbooks and each time it opens a new one, the custom function kicks off and runs, consuming time...

Anyone have a solution?

figment
04-18-2008, 02:03 PM
try using

Application.EnableEvents = False

to keep the open event from triggering multiple times

and

Application.EnableEvents = True


so that events will trigger again

djchou
04-18-2008, 02:11 PM
Didn't work.

Whenever the macro opens another workbook, it still wants to re-run the custom function.

Here's the custom function -- True if a file exists in the path of the cell / False if it does not exist.

Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function


Aussiebear: Edited to include the Function within the vba tags

figment
04-18-2008, 02:15 PM
what calls this function?

mostlikly you need to put the event controls in the sub that calls this function.

djchou
04-18-2008, 02:16 PM
not sure what you mean, the function automatically runs itself whenever another workbook is opened.

figment
04-18-2008, 02:29 PM
the code that you posted is a function. this means something has to call it. the problem is in what ever is calling the function, not the function. this function only checks to see if a file exits. i dosn't open a file, so you need to find what opens the file.

djchou
04-18-2008, 02:35 PM
I posted the application.enableevents=false in the macro that opens the workbooks.

Try replicating the problem...
New workbook - in range A1 - type the path of any file. i.e. C:\test.xls
in range b1, enter the custom function "=filethere(a1)

In vba, add the custom function to a new module
add the macro below, and step through... you'll see that the macro below runs the custom function whenever the worbooks.open line runs.

Sub loadweights()
home = ActiveWorkbook.Name
Workbooks.Open FileName:=Range("a1").Value
weightsbook = ActiveWorkbook.Name
End Sub

Aussiebear: Edited to include Code within the vba tags

figment
04-18-2008, 02:48 PM
sorry i didn't realize you were calling the function from a cell.but being that you are calling the function from a cell then the function will run every time the work book recalculates its cells. To stop it from calculating when you open files set calculations to manual mode then once you have the files open you can set calculate back to normal

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

djchou
04-18-2008, 02:59 PM
that works.

Thanks