Need to modify a User Defined Function

[sunny17]

I'm a newbie with VBA. I've tried writing a user defined function :

Public Function SplitNames(NamesCell As Range)
    Dim vecNames As Variant
    Dim cell As Variant
    Dim yourPcge As String
    Dim tmp As String
     
    vecNames = Split(NamesCell.Value, ",")
    For Each cell In vecNames
         
        yourPcge = "=IF(A5="","",IF(SUMPRODUCT(--($A$2:$A5&$F$2:$F5=A5&F5))=1,(20-H5)/20,(20-SUMIF($F$2:$F5,F5,$H$2:$H5))/20))"
        tmp = tmp & cell & " : " & yourPcge & " ; "
    Next cell
     
    SplitNames = Left$(tmp, Len(tmp) - 3)
End Function

Now in the line,

yourPcge = "=IF(A5="","",IF(SUMPRODUCT(--($A$2:$A5&$F$2:$F5=A5&F5))=1,(20-H5)/20,(20-SUMIF($F$2:$F5,F5,$H$2:$H5))/20))"
        tmp = tmp & cell & " : " & yourPcge & " ; "

The formula is actually an excel formula. How do I change it so that it fits into the user defined function and gives me the expected result?

[kaliman79912]

It would be good if you explain what that formula is trying to do. with all the sumproduct, sumif and if it gets confusing. Another thing is that it has relative and absolute references so coding it would be affected by that.

[Siddharth Rout]

Hello Sunny

Since you are beginning with VBA, I would suggest you to get the basics strong This will help you in creating functions like above. I would recommend you to read this article.

Topic: Using Microsoft Excel Worksheet Functions in Visual Basic [Excel 2003 VBA Language Reference]
Link: http://msdn.microsoft.com/en-us/library/aa221602%28v=office.11%29.aspx

Also I would suggest you not to use the Variant Type of variable unless it is necessary. Variants slow down your code as the VB Compiler takes time to decide on what kind of variable you are using. Variants should also be avoided as they are responsible for causing possible “Type Mismatch Errors”. It’s not that we should never use Variants. They should only be used if you are unsure what they might hold on code execution.

In the above case you can use it as below

    vecNames = Split(NamesCell.Value, ",")
    
    For i = 0 To UBound(vecNames)
    
    Next i

Here is an interesting read on Variants.

Topic: Internal Representation of Values in Variants
Link: http://msdn.microsoft.com/en-us/library/aa261347%28v=vs.60%29.aspx

I would further suggest you to use Error handling. If you are developing for a client then your client will be left high and dry as the code will break leaving your client clueless as to what exactly went wrong. you also need to do appropriate checks to ensure that your code doesn't break. For example consider this line in your code

vecNames = Split(NamesCell.Value, ",")

What if NamesCell.Value doesn't contain a "," ?

I suggest this

Public Function SplitNames(NamesCell As Range) As String
    Dim vecNames() As String, yourPcge As String, tmp As String
    Dim i As Long
    
    If InStr(1, NamesCell.Value, ",") Then
        vecNames = Split(NamesCell.Value, ",")
        '
        '~~> Rest of the code
        '
    Else
        SplitNames = "Not Found"
    End If
End Function

Now to your original question. We can tackle it in two ways.

1) The Simplest Way: Since the value of yourPcge is not changing inside the loop, you might want to move it out of the loop. This will ensure that the code will run faster
You can use a helper cell in the worksheet which will hold that formula and then you can pass that in your function. For example, Let's say Cell M10 has that formula then you can pass it to the function as below

=SplitNames(A1,M10)

Option Explicit

Public Function SplitNames(NamesCell As Range, RngFormula As Range) As String
    Dim vecNames() As String, yourPcge As String, tmp As String
    Dim i As Long
    
    On Error GoTo Whoa
    
    yourPcge = RngFormula.Value
    
    If InStr(1, NamesCell.Value, ",") Then
        vecNames = Split(NamesCell.Value, ",")
        
        For i = 0 To UBound(vecNames)
            tmp = tmp & vecNames(i) & " : " & yourPcge & " ; "
        Next i
        
        SplitNames = Left$(tmp, Len(tmp) - 3)
    Else
        '~~> Change this text to what ever you want to display incase there is no ',' in the NamesCell
        SplitNames = "Not Found"
    End If
    
    Exit Function
Whoa:
    '~~> Change this text to what ever you want to display incase there is no ',' in the NamesCell
    SplitNames = "Not Found"
End Function

2) The Difficult Way: For this you will have to calculate the value of yourPcge in side the function with the help of .WorksheetFunction(). I would suggest you to go through the first link that I gave above.

I am not sure what that formula is supposed to do else I would have given you a sample

Hope this helps.

Sid