CODEWITHSUNDEEP
exceluser-defined-functionsvba
AntiDrondert
AntiDrondert

Reputation: 1149

Excel UDF doubles value of the evaluated SUB

1. I was trying to answer VBA UDF to split string array and got an unpleasant results during computing my UDF. 

Public Function mytest(src, dest)
    dest.Parent.Evaluate "test(" & src.Address(False, False) & ", " & dest.Address(False, False) & ")"
    mytest = "wut"
End Function

Sub test(src As Range, dest As Range)
    Dim chr, rows, cols
    rows = 0
    cols = 0
    For chr = 1 To Len(src.Value)
        Select Case Mid(src.Value, chr, 1)
            Case ","
            rows = rows + 1
            Case ";"
            cols = cols + 1
            rows = 0
            Case Else
            Cells(dest.Row + rows, dest.Column + cols).Value = Cells(dest.Row + rows, dest.Column + cols).Value & Mid(src.Value, chr, 1) '
        End Select
    Next chr
End Sub

Expected results: enter image description here Formula results: enter image description here Can someone explain why does it double Value of the cell?
When I debugged test using 

Sub ffs()
    Call test(Cells(1, 1), Cells(3, 1))
End Sub

I got expected results, so I guess the problem is not in the test Sub?..

2. Whenever I try to add more parameters to Function and Sub (for example delimiters) Function doesn't Evaluate Sub at all 

Public Function CellToRange(src, dest, DelimL, DelimC)
    dest.Parent.Evaluate "test(" & src.Address(False, False) & ", " & dest.Address(False, False) & ", " & DelimL & ", " & DelimC & ")"
    CellToRange = "wut"
End Function

Sub CTR(src As Range, dest As Range, Delim1, Delim2)
    Dim chr, rows, cols
    rows = 0
    cols = 0
    For chr = 1 To Len(src.Value)
        Select Case Mid(src.Value, chr, 1)
            Case Delim1
            rows = rows + 1
            Case Delim2
            cols = cols + 1
            rows = 0
            Case Else
            Cells(dest.Row + rows, dest.Column + cols).Value = Cells(dest.Row + rows, dest.Column + cols).Value & Mid(src.Value, chr, 1) '
        End Select
    Next chr
End Sub

Please help ._. and thanks in advance.
Solution:
Thanks Billy and Charles Williams.
Change

dest.Parent.Evaluate "CTR(" & src.Address(False, False) & ", " & dest.Address(False, False) & ", " & DelimL & ", " & DelimC & ")"

To

dest.Parent.Evaluate "0+CTR(" & src.Address(False, False) & ", " & dest.Address(False, False) & ", " & DelimL & ", " & DelimC & ")"

Thanks everyone!

Upvotes: 0

Views: 217

Answers (2)

DMM
DMM

Reputation: 1112

The problem lies with the Worksheet.Evaluate method which is being used to get round the restriction that a UDF is not allowed to modify the worksheet structure.

Consider this code

Option Explicit

Public Function dummyudf() As String
    Debug.Print "Calling Evaluate method"
    ActiveSheet.Evaluate "testsub()"
    Debug.Print "Returning From Evaluate method"
    dummyudf = "done"
End Function

Sub testsub()
    Debug.Print "testsub running"
End Sub

Sub testmacro()
    Dim s As String
    Debug.Print "testmacro running"
    s = dummyudf
End Sub

The UDF dummyudf() uses the Evaluate method to invoke the Sub called testsub(). These are analagous to mytest and test in part 1. of the OP and to CellToRange and CTR in part 2 but are stripped down to the bare minimum.

testsub() can also be invoked directly as a macro. A second macro testmacro invokes dummyudf as a function in VBA.

The following output was obtained from the Immediate Window:

Output from Immediate Window

As can be seen

  • when invoked as a macro: testsub() behaves as expected

  • when dummyudf() is invoked as a UDF on the worksheet (for example by adding the formula =dummyudf() to cell A1 the Evaluate method appears to call testsub() twice

  • when dummyudf() is invoked as a function in VBA by running testmacro() as a macro the Evaluate method appears to call testsub() twice.

The documentation here suggests that the Name argument of the Worksheet.Evaluate method should be the name of an object, so it is a bit surprising that it is possible supply the name of a Sub. That it also seems to call any such Sub twice, is even more surprising but does underline the advice given in YowE3K's answer about not using this hack in a UDF. I'd go further: don't use Worksheet.Evaluate with any Sub.

Upvotes: 3

YowE3K
YowE3K

Reputation: 23974

1) It evaluates once when the formula is triggered, and again when cell A3 is updated by the function (as it is one of the cells the formula is dependent on).

2a) You are calling the wrong subroutine (test instead of CTR)

2b) You need to call your second function using something like 

=CellToRange(A1;A3;""",""";""";""")

or else change the line in your code calling CTR to be

dest.Parent.Evaluate "CTR(" & src.Address(False, False) & ", " & dest.Address(False, False) & ", """ & DelimL & """, """ & DelimC & """)"

3) I strongly recommend that you do not use this sort of hack to get a UDF to update cells other than the one containing the function.

Upvotes: 1

Related Questions