none
Copy Results of a User Defined Functions Argument to a table RRS feed

  • Question

  • Hi guys,

    I have a user defined formula that has 3 different arguments as follows: myUDF (Value as variant, ValueType as variant, PeriodStamp as variant).

    Each of the arguments can be a reference to a value in a specified range (e.g. $C$5 where cell C5 contains the value "RicRevenue") or the string itself (i.e. in this case "RicRevenue"). I would like to write a macro that copies the results of each of the arguments in myUDF to a table with columns for value, value type and period stamp.

    I have used getformula to capture the formula in the cell, but am unsure of how to parse that and get the values.

    Any help much appreciated.

    Ric

    Sunday, May 21, 2017 8:01 PM

Answers

  • Hi Ric,

    You could get arguments text from the cell's formula and check if it is a range or a string. Here is the example.

    fString = Range("A1").formula
    fString = Mid(fString, 8, Len(fString) - 8)
    Debug.Print fString
    Dim strList As Variant
    strList = Split(fString, ",")  'get Argument List
    For Each arg In strList  'loop the list check if Argument is a range or a string
    Debug.Print ReValue(arg)  'return value
    Next arg
    
    
    Function ReValue(str As Variant)
    Dim rangecheck As Range
    On Error Resume Next
    Set rRangeCheck = Range(str)
    If IsEmpty(rRangeCheck) Then
         ReValue = str
    Else
         ReValue = rRangeCheck.Value
    End If
    End Function
    

    Besides, I suggest you create global variables for getting the arguments in the formula code. Here is the example.

    Public v1 As String
    Public v2 As String
    Public v3 As String
    
    Function myUDF(Value As Variant, ValueType As Variant, PeriodStamp As Variant)
    v1 = Value
    v2 = ValueType
    v3 = PeriodStamp
    myUDF = 1
    End Function
    
    Sub Result()
    Debug.Print v1
    Debug.Print v2
    Debug.Print v3
    End Sub
    

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by RicG77 Friday, May 26, 2017 8:13 AM
    Tuesday, May 23, 2017 7:13 AM
    Moderator

All replies

  • Hi Ric,

    You could get arguments text from the cell's formula and check if it is a range or a string. Here is the example.

    fString = Range("A1").formula
    fString = Mid(fString, 8, Len(fString) - 8)
    Debug.Print fString
    Dim strList As Variant
    strList = Split(fString, ",")  'get Argument List
    For Each arg In strList  'loop the list check if Argument is a range or a string
    Debug.Print ReValue(arg)  'return value
    Next arg
    
    
    Function ReValue(str As Variant)
    Dim rangecheck As Range
    On Error Resume Next
    Set rRangeCheck = Range(str)
    If IsEmpty(rRangeCheck) Then
         ReValue = str
    Else
         ReValue = rRangeCheck.Value
    End If
    End Function
    

    Besides, I suggest you create global variables for getting the arguments in the formula code. Here is the example.

    Public v1 As String
    Public v2 As String
    Public v3 As String
    
    Function myUDF(Value As Variant, ValueType As Variant, PeriodStamp As Variant)
    v1 = Value
    v2 = ValueType
    v3 = PeriodStamp
    myUDF = 1
    End Function
    
    Sub Result()
    Debug.Print v1
    Debug.Print v2
    Debug.Print v3
    End Sub
    

    Regards,

    Celeste



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by RicG77 Friday, May 26, 2017 8:13 AM
    Tuesday, May 23, 2017 7:13 AM
    Moderator
  • This was super useful. Thanks for the help!
    Friday, May 26, 2017 8:13 AM