Excel UDF to update a string in another cell and return a string

Question

question

MannyPereira-0526 asked Mar 13, '21 MannyPereira-0526 answered Mar 19, '21

Excel UDF to update a string in another cell and return a string

I would like to have a User Defined Function that has three cell reference arguments and depending on a value of first cell, the value of the first or second cell is returned and the non-returning value is placed in the third cell reference. I have this that gives me an error. Cell formula: =GameSet(D6;D8;K5) Function GameSet(Player1 As String, Player2 As String, Loser As String) As String Dim Temp As String If Player2 = "Bye" Then Temp = Player1 Loser = Player2 End If GameSet = Temp End Function

office-excel-itpro

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

erinding-msft · Mar 15, 2021 at 09:50 AM

Hi @MannyPereira-0526
Please check OssieMac's reply.
It's suggested to add a tag "office-vba-dev".
Any updates, please let us know.

0 ·

Answer 1 · 2021-03-13T11:33:23.33Z

OssieMac answered Mar 13, '21 OssieMac edited Mar 13, '21

Not really sure what you are attempting to do. AFAIK only the cell that calls the UDF can be updated by the UDF. Other cells cannot be updated from the UDF

However, if passing cell references as the arguments to the UDF then the cell references must be ranges, not strings. You can then assign the values from the ranges to string variables as per the following. (Or you could use rng1.value, rng2.value etc directly without first assigning to string variables.

The following example does not update another cell because that cannot be done.

Note: In my regions we use commas (not semicolons) as delimiters between the the arguments so you will need to edit these.

  Function GameSet(rng1 As Range, rng2 As Range, rng3 As Range) As String
     Dim strPlayer1 As String
     Dim strPlayer2 As String
     Dim strLoser As String
     Dim Temp As String
        
     strPlayer1 = rng1.Value
     strPlayer2 = rng2.Value
     strLoser = rng3.Value
        
     If strPlayer2 = "Bye" Then
         Temp = strPlayer1
         strLoser = strPlayer2
     End If
     GameSet = Temp
 End Function

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2021-03-19T07:13:58.907Z

MannyPereira-0526 answered Mar 19, '21

Thank you OssieMac for highlighting the fact that a UDF cannot update any other cell but can return a value or array. I have managed to sort out my problem by having two UDF, one returning the Winner and the other returning the Loser.
Manny

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question