Custom Function Argument with cell reference

Custom Function Argument with cell reference

Posted by: kevin.keller on 12 April 2019, 3:01 am EST

• 

  kevin.keller

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 12 April 2019, 3:01 am EST

  Hello,

  I have a Custom function created let’s say it is CUSTVAL and when I use it I have a formula like =CUSTVAL(D4) the argument being sent to the Evaluate Function is the value of the D4 cell. What I would like is to be able to see what the location of D4 is, such as column and row indexes.

  Is there a way to get the actual initial argument of D4 not the value of that cell?

  Along those same lines, is there a way to have that initial argument and be able to determine it is a valid cell reference? (this would accomplish my need noted above of getting the Column index)

  The reason I am looking into this is, some of our custom functions that we will need rely on a cell pointer but can also take literal values, and we would have our Evaluate Function handle this.

  Thanks,

  Kevin

• 

  kevin.keller

  • Post Options:
  • Link
    Copy

  Posted 25 April 2019, 2:44 am EST

  Any information on this?

• 

  kevin.keller

  • Post Options:
  • Link
    Copy

  Posted 25 April 2019, 5:39 am EST

  I think I found what is needed.
  
  Had to add an override to the function declaration and specify returning true for the argument(s) that could be a cell reference that is needed.

  Public Overrides Function AcceptsReference(i As Integer) As Boolean
      If i = 0 Then
          Return True
      Else
          Return MyBase.AcceptsReference(i)
      End If
  
  End Function
  

• 

  Ruchir.Agarwal

  

  • Post Options:
  • Link
    Copy

  Posted 29 April 2019, 10:56 pm EST

  Hello,

  Apologies for missing out your post.
  
  Regarding your requirement, we’re glad that you were able to figure out a solution.
  
  Also, we thank you for sharing the solution here as it might help other community members having the same requirement.

  Thanks,
  
  Ruchir