Noob Cant make a recursive function in vb for excel

[Borsuk Ulam]

Hi, im trying to write a recursive function to reformat some data in excel.
This is my first function i ever wrot using vb.
I have used Scheme in school when learning how to write recursive functions.

this is what i wrote


Function f(cell)
If cell = "" Then
f = cell
ElseIf Left(cell, 1) = " " Then
f = f(Replace(cell, 1, 1, ""))
ElseIf Left(cell, 1) = "," Then
f = f(Replace("cell", 1, 1, ""))
ElseIf Left(cell, 1) = "." Then
f = f(Replace(cell, 1, 1, ""))
ElseIf Left(cell, 54) = "BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE, EXPIRY DATE" Then
f = "2 " & " " & f(Replace(cell, 1, 54, ""))
ElseIf Left(cell, 40) = "BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE" Then
f = "1 " & " " & f(Replace(cell, 1, 40, ""))
ElseIf IsNumeric(Left(cell, 1)) Then
f = f(Replace(cell, 1, 1, ""))
ElseIf left(cell,2) = "TO" Then
f(Replace(cell,1,2,"")
f = Left(cell, 4) & " " & f(Replace(cell, 1, 4, ""))
End If
End Function

My function if given

TP21, TP75, TP05, TP19, TMT1, T115, TP11, TP01 TO 20110802, BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE, EXPIRY DATE TP79, TL59 TO 20110802

should produce
TP21 TP75 TP05 TP19 TMT1 T115 TP11 TP01 2 TP79 TL59

instead i get #value

If there was more information i should provide, let me know.

Thanks for your help.

 

[shg]

Welcome to the board.

Can you explain, without reference to your code, what the function is supposed to do?

 

[Borsuk Ulam]

Hi,

My code is supposed to take a list of products for which certain dates need to be updated to be updated to a given date and simplify it.

These look like:
TP21, TP75, TP05, TP19, TMT1, T115, TP11, TP01 TO 20110802, BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE, EXPIRY DATE TP79, TL59 TO 20110802

TP07, TP05. TP19, TMT1 TO 20110802,BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE, EXPIRY DATE TL59 TO 20110802,BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE TP79 TO 20110802

The respective outputs should look like
TP21 TP75 TP05 TP19 TMT1 T115 TP11 TP01 2 TP79 TL59

TP07 TP05 TP19 TMT1 2 TL59 1 TP79

Each product has a 4 digit code which begins with a letter. E.g. 'TP01'
products which come before BOTH PRODUCT AND DISCO............... all will require only 2 dates be changed.

Products which are preceded by BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE require 3 dates to be changed. I want to condense this statement to the character "1"

Products which are preceded by BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE, EXPIRY DATE require 4 dates to be changed. I want to condense this statement to the character "2"

My function should produce the list of products delimited with spaces (removing additional spaces, commas, periods) between products, potentially with a 1 and/or a 2 somewhere in the middle dividing the list into the 3 cases which are handled differently.

The function works recursively as each account will need a different number of products handled in each of the 3 ways.

My Idea was to check if the first thing is something i want to remove, if so recurs on the rest of the cell. I then check if the cell begins with the long string, If so return "2 " concatenated to the recurse on the rest the long string with 2. Then check if it begins with the short string, if so return "1 " concatenated to the recurs on the rest. Then check if the first character is numeric, and recurs on the rest if it is. Then check if the first 2 characters are TO and re curse on the rest if they are. And finally, if its not something I want to get rid of it is a product code, so i take the first 4 characters concatenate a space after them and concatenate recursion on the rest after that.

 

[shg]

Recursion aside, how about ...

Function BU(ByVal sInp As String) As String
    Dim asInp()     As String
    Dim i           As Long
 
    sInp = WorksheetFunction.Trim(Replace(Replace(UCase(sInp), ",", " "), ".", " "))
    sInp = Replace(sInp, "BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE EXPIRY DATE", "2")
    sInp = Replace(sInp, "BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE", "1")
    asInp = Split(sInp, " ")
 
    For i = 0 To UBound(asInp)
        Select Case Len(asInp(i))
            Case 1
                If Not IsNumeric(asInp(i)) Then asInp(i) = vbNullString
            Case 4
            Case Else
                asInp(i) = vbNullString
        End Select
    Next i
 
    BU = WorksheetFunction.Trim(Join(asInp, " "))
End Function

 

[p45cal]

You seem to be trying to use Replace with the same arguments you'd use on a sheet - they're different. Further, you seem to be using it always to remove characters from the left of a string, Use Mid instead, with the last argument at least as big as you need; I used Len(Cell) which is guaranteed to be long enough.
Sticking to your recursion idea (not necessarily what I'd do) the following seems to work but I haven't explored its robustness:

Function f(cell)
If cell = "" Then Exit Function
Select Case True
    Case Left(cell, 1) = " " Or Left(cell, 1) = "," Or Left(cell, 1) = "." Or IsNumeric(Left(cell, 1))
        f = f(Mid(cell, 2, Len(cell)))
    Case Left(cell, 2) = "TO"
        f = f(Mid(cell, 3, Len(cell)))
    Case Left(cell, 53) = "BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE, EXPIRY DATE"
        f = "2 " & f(Mid(cell, 54, Len(cell)))
    Case Left(cell, 40) = "BOTH PRODUCT AND DISCOUNT EFFECTIVE DATE"
        f = "1 " & f(Mid(cell, 41, Len(cell)))
    Case Else
        f = Left(cell, 4) & " " & f(Mid(cell, 4, Len(cell)))
End Select
End Function

 

[Borsuk Ulam]

Hi,

Thanks to SHG and P45Cal for their quick responses.

SHG, As this is my first time experimenting with vb, I'm having alot trouble understanding how your function works. I would appreciate if you would explain how it would work step by step as I can tell you are very comfortable with vb.

P45Cal's function is closer to plain english as well as to my function. I quickly understood the changes he made to my original code and how his finished code works. As left, right and mid all work the same in vb as in excel, as far as i know, i find it a little surprising that replace doesn't. Thank you for informing me.

Unfortunately I've left work for the weekend now am at home. I will test both codes on Monday.

Thanks again for your help!

 

[Borsuk Ulam]

Hi,

Neither of these seem to work.

Both give me '#NAME?' as output