PDA

View Full Version : Automation error (Error 440)



DevanG
06-29-2011, 10:29 AM
So, I get the Automation error (Error 440)

when I try to run a workbook through a few different subs. And yes, i cut out lots of code -- these 3 DO need to be separate.
Public Sub cmdInvest7_Click()
Dim InvestAllo As Workbook
TheFile = Application.GetOpenFilename(FileFilter:="XLS (Comma delimited)(*.xls), *.xls", Title:="Please select a file")
Set InvestAllo = Workbooks.Open(filename:=TheFile)
RKrev = RunRepricing(InvestAllo, TheFile)
End Sub

Public Function RunRepricing(contract As Workbook, filename As String) As Double
Call getAutoSiebelData(contract)
end sub

Private Sub getData(investalloc As Workbook)
dim contract as double
contract = investalloc.Sheets(1).Range("b4").Value ' **FAILS HERE**
end sub

How do I get around this error?

Kenneth Hobs
06-29-2011, 10:51 AM
I guess you would get around it by coding for all scenarios that might produce it. Since you don't post all of the code and workbooks, there is no way for us to see what else might be going on should everything be done just right.

If you are not the user of the code, it is likely that your end user did something that you did not count on. It is highly recommended that you use Option Explicit.

As in your other threads, you should understand that the return variable may not be the type that you expect. Case in point here is the return value for the GetOpenFilename(). It should return a Variant as detailed in the help. Your function expects a valid filename string so be sure to use Cstr() to convert the variant to a string but I would not even call it if the result of GetOpenFilename() was not an existing file. You will also need to check the filename chosen is not one that is open already as well.