VBA Function not Returning Value

Question

I have a VBA code that's designed to search a CSV String and add Carriage Returns where they should exist. I've split it up into two seperate functions - one to search the string and put the index of where the CRs should go into an array and a second function to actually add the CRs.

The issue I'm running into is that the value in the immediate window/in the watch window for the functions is correct within the function itself, but it assigns the result variable a blank string.

'*****************Import CSV**********************
'Took this straight off the internet because it was reading Jet.com files as one single line
'
Sub ImportCSVFile(filepath As String)

    .....
    line = SearchString(line, "SALE")
    .....

End Sub

'****************Search String***************************
    'This is search the string for something - It will then call a function to insert carriage returns

Function SearchString(source As String, target As String) As String

Dim i As Integer
Dim k As Integer
Dim myArray() As Variant

Dim resultString As String

Do
    i = i + 1
    If Mid(source, i, Len(target)) = target Then
        ReDim Preserve myArray(k)
        myArray(k) = i
        k = k + 1
    End If
DoEvents
Loop Until i = Len(source)

resultString = addCarriageReturns(source, myArray) 'resultString here is assigned a blank string
SearchString = resultString

End Function

'***************Add Carraige Returns**************************
'Cycle through the indices held in the array and place carriage returns into the string


Function addCarriageReturns(source As String, myArray As Variant) As String

Dim i As Integer
Dim resultString As String

resultString = source

For i = 0 To UBound(myArray, 1)
    resultString = Left(resultString, myArray(i) + i) & Chr(13) & Right(resultString, Len(resultString) - myArray(i) + i)
Next i

addCarraigeReturns = resultString 'The value of addCarriageReturn is correct in the immediate window here

End Function

In the function the value is not blank ...but when it passes it back, it says the value is blank

Check your spelling: addCarriageReturns vs addCarraigeReturns — Gordon Bell
– Gordon Bell, Commented Jul 26, 2017 at 19:23
Save yourself the headaches, and never do VBA without "Option Explicit" — Gordon Bell
– Gordon Bell, Commented Jul 26, 2017 at 19:30

Gordon Bell · Accepted Answer · 2017-07-26 19:29:42Z

1

I'm just curious, why do you want separate functions like this?

Can you just use:

line = Replace(line, "SALE", "SALE" & Chr(13))

answered Jul 26, 2017 at 19:29

Gordon Bell

13.7k3 gold badges48 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BobtimusPrime Over a year ago

Because apparently I'm a masochist and do things the harder way. :) I forgot that replace isn't destructive to the rest of the string, but only affects the part you specify.

Gordon Bell Over a year ago

Not to worry, happens to all of us. :)

Collectives™ on Stack Overflow

VBA Function not Returning Value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related