question

MannyPereira-0526 avatar image
0 Votes"
MannyPereira-0526 asked MannyPereira-0526 answered

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

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

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 Votes 0 ·
OssieMac avatar image
0 Votes"
OssieMac answered OssieMac edited

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

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

MannyPereira-0526 avatar image
0 Votes"
MannyPereira-0526 answered

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

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