AussieMick
08-15-2017, 10:47 AM
Hi,
I'm using Excel from Office 2010 with Windows 10. I'm having a problem getting a #1004 error - application or object undefined.
From what I've read, I'm pretty sure that the problem could be an object reference which does not have enough context to be resolved. I've tried quite a number of things, but so far no joy. My apologies, but I can't figure out what else I either need to do or could do and am hoping for some help.
I've put together a basic function which illustrates the problem, in the attached Test.xlsm
My workbook has two sheets - MyGraphs and MyData. On MyGraphs are two cells - A2 which has the parameter and B2 which has "=My2X(A2)" . As well as returning the formula result in B2, the function should also update MyData!A1 with a string value.
If I run the function from the immediate window using k = My2x(4) then, after failing the first time of having the function edited, it runs and Debug.Print k gives 8, as expected.
Using the function from the worksheet MyGraphs results in the #1004 error each time.
If the line "wsMyData.Cells(1, 1).Value = "Executed My2X: " & intX & " twice is " & i2X" is commented out it works.
Best regards,
Michael.
Function My2X(intX As Integer) As Integer
On Error GoTo error_My2X
Dim intErrCnt As Integer
intErrCnt = 0
Dim i2X As Integer
Dim wsMyData As Worksheet
i2X = intX + intX
Set wsMyData = ThisWorkbook.Worksheets("MyData")
wsMyData.Cells(1, 1).Value = "Executed My2X: " & intX & " twice is " & i2X
My2X = i2X
exit_My2X:
Set wsMyData = Nothing
Exit Function
error_My2X:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
intErrCnt = intErrCnt + 1
If intErrCnt < 4 Then 'Stops looping in the error handler, if it is the exit handler throwing the exception
Resume exit_My2X
Else
Exit Function
End If
End Function
I'm using Excel from Office 2010 with Windows 10. I'm having a problem getting a #1004 error - application or object undefined.
From what I've read, I'm pretty sure that the problem could be an object reference which does not have enough context to be resolved. I've tried quite a number of things, but so far no joy. My apologies, but I can't figure out what else I either need to do or could do and am hoping for some help.
I've put together a basic function which illustrates the problem, in the attached Test.xlsm
My workbook has two sheets - MyGraphs and MyData. On MyGraphs are two cells - A2 which has the parameter and B2 which has "=My2X(A2)" . As well as returning the formula result in B2, the function should also update MyData!A1 with a string value.
If I run the function from the immediate window using k = My2x(4) then, after failing the first time of having the function edited, it runs and Debug.Print k gives 8, as expected.
Using the function from the worksheet MyGraphs results in the #1004 error each time.
If the line "wsMyData.Cells(1, 1).Value = "Executed My2X: " & intX & " twice is " & i2X" is commented out it works.
Best regards,
Michael.
Function My2X(intX As Integer) As Integer
On Error GoTo error_My2X
Dim intErrCnt As Integer
intErrCnt = 0
Dim i2X As Integer
Dim wsMyData As Worksheet
i2X = intX + intX
Set wsMyData = ThisWorkbook.Worksheets("MyData")
wsMyData.Cells(1, 1).Value = "Executed My2X: " & intX & " twice is " & i2X
My2X = i2X
exit_My2X:
Set wsMyData = Nothing
Exit Function
error_My2X:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
intErrCnt = intErrCnt + 1
If intErrCnt < 4 Then 'Stops looping in the error handler, if it is the exit handler throwing the exception
Resume exit_My2X
Else
Exit Function
End If
End Function