Function to update a cell in another worksheet giving #1004 error

[AussieMick]

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

[p45cal]

It depends how the function is called.
Functions are supposed to return a value(s), not do things.
Calling it from a worksheet means it will only return a value.
Try calling it from another sub and you'll find it may well do things too!
Sub testme()
Dim x, i As Integer
For i = 1 To 10
x = My2X(i)
Next i
End Sub

[Paul_Hossler]

User Defined Functions (UDF) really can't update WS parameters (color, font, etc.)

You can use an array to return multiple parameters of mixed type as possible workaround

You need to select the cells (yellow) and 'array enter' the formula using ctrl-shift-enter which adds the braces - you do not actually type them

20085

BTW, I think that something like your UDF should be in a standard module, not a worksheet module -- see attachment




Option Explicit
Function My2X(intX As Integer) As Variant

Dim v(1 To 2) As Variant
Dim i2X As Integer
i2X = intX + intX

v(1) = "Executed My2X: " & intX & " twice is " & i2X
v(2) = i2X

My2X = v
End Function

[AussieMick]

Ok, thanks - I think I see. Assigning the function to a worksheet cell implicitly limits its execution to that sheet's context. So to do what I want, the executing module needs to be "outside" a single sheet. This would perhaps explain another puzzling thing - when I was trying things, one of them was to make MyData the active worksheet - I used the method wsMyData.Activate. But then checking that this had happened by getting Thisworkbook.Activesheet.Name always returned MyGraphs. Using wsMyData.Activate did not cause an exception, but just seemed to be ignored.

Regards,
Michael

[p45cal]

Assigning the function to a worksheet cell implicitly limits its execution to that sheet's context.It's more than that. A formula in a cell will not do anything but change the result in that cell. The attempt to activate another sheet is one of those things it won't do. Changing the value in another cell is another one of those things it won't do. As it happens, the former doesn't throw an error, the latter does.

[AussieMick]

Ok, thanks, Paul - I didn't think of it as a multi-valued return thing.