0

For a project of mine I need to get the cell address of the cell a user chooses as source for the argument for my function:

Public Function ItemNumber(AnyValueFromAnyCell) As String
End Sub

If the user selects, say, cell F6 as the source for "AnyValueFromAnyCell" when using the function, I need its address to also manipulate that cell.

The idea is that if someone uses my new function, the function changes the fillcolour from the source cell too.

I hope this is clear enough.

Community
  • 1
  • 1
NamSandStorm
  • 155
  • 7
  • 2
    You will not be able to change the color of the source cell using a UDF. – YowE3K Nov 14 '16 at 20:13
  • I retract my comment - I just found [Tim William's solution](http://stackoverflow.com/a/23437280/6535336) that **does** allow a UDF to modify another cell. (But it should be used at your own risk!) – YowE3K Nov 15 '16 at 00:54

3 Answers3

2

I would declare the data type of the arg as a Range and then you can access the value and the address

Public Function ItemNumber(val As Range) As String
Dim addressOfCell
Dim valueOfCell
    addressOfCell = val.Address 'address of cell
    valueOfCell = val.Value   'value in the cell
End Function
Sorceri
  • 7,870
  • 1
  • 29
  • 38
  • 1
    Maybe include the fact that value being the default value of range, should probably be avoided as an argument name. – TZubiri Nov 14 '16 at 19:51
  • @Sorceri, it worked for a second return "$F$6" for Value.Address ... now it gives me "compile error, invalid use of property – NamSandStorm Nov 14 '16 at 20:10
  • @NamSandStorm That is most likely because you didn't assign anything, I have updated the answer – Sorceri Nov 14 '16 at 20:14
  • @Sorceri ... my bad ... where is the hole to climb into ... I have been battling with this for two hours now and cant seem to think straight. Thanks for the help – NamSandStorm Nov 14 '16 at 20:16
1

Extending a little bit Sorceri's answer above to allow the function to be used either from a cell formula or as a helper in the code, and to display other parts of the code that were asked for in the main question:

Public Function ItemNumber(arg As Variant) As String
Dim addressOfCell, valueOfCell
Dim rnSource As Range, rnCaller As Range

 On Error Resume Next

 valueOfCell = arg
 set rnSource = IIf(TypeName(arg) = "Range", arg, Nothing)
 addressOfCell = vbNullString
 If Not (rnSource Is Nothing) Then  addressOfCell = rnSource.Address

 Set rnCaller = Application.Caller
 If Not (rnCaller Is Nothing) then
     ' queue task here to change the interior color of the cell calling the function to be executed, as per the following solution:
     ' Source: https://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change/8711582#8711582
 End If
End Function
rellampec
  • 698
  • 6
  • 22
0
Dim sAdd as String, val as Variant

sAdd = Selection.Address
val = Selection.Value

Dim sReturn as String
sReturn = ItemNumber(sAdd, val)

Then add an argument to your function to return accept the address as string.

Function ItemNumber(sAddress as String, Value) As String
End Function
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Thanks Scott, you kinda lost me. Please excuse me, I am not quite a coding Ninja level. Where does the top code go? – NamSandStorm Nov 14 '16 at 19:47
  • @NamSandStorm - Well, I guess it's not 100% clear what you need the function for, because you didn't provide. But assuming you want to manipulate things in the function (as you wrote) i provided a description of how to pass arguments into the function based on user selection. All what I wrote above would go into a separate procedure. Sub MySub() for example – Scott Holtzman Nov 14 '16 at 19:50