CODEWITHSUNDEEP
excelvba
Christian Prieto
Christian Prieto

Reputation: 73

Return Function's Value Issue

The goal of this code is to count the number of columns on a table of values and return the value of CC from the function FTColSize(CC) to FTLC. The CC returns the correct value but FTLC returns 0.

'FTLC ~ First Table Last Column
'CC ~ Column Counter

Sub OneHundred_Percent()
 Dim FTLC%, CC%
  CC = 1
  FTLC = FTColSize(CC)
 Debug.Print "First Table Last Column Coordinate"; FTLC
End Sub

Function FTColSize(CC) As Integer
    Debug.Print "Outside of IF where CC: "; CC
    Debug.Print "Outside of IF statement the value IsEmpty at (1, CC) where CC = "; CC; " is "; IsEmpty(Cells(1, CC).Value)
    If IsEmpty(Cells(1, CC + 1).Value) = False Then
        Debug.Print "Inside If Statement"
        Debug.Print "Incriment CC: "; CC
        CC = CC + 1
        Debug.Print "CC is now "; CC; " inside IF"
        FTColSize (CC)
    Else
        Debug.Print "Inside Else Statement"
        Debug.Print "CC is now "; CC; " inside Else"
        'Return CC
        FTColSize = CC
        Debug.Print FTColSize
        Exit Function
    End If
End Function

CC returns as the number of columns in a table in the function procedure. FTLC returns as 0 and I don't understand why. I've tried so many different approaches and when I attempt to return a value the macro tells me it is missing end of statement. does anybody have an idea on how to return CC from the function? I want FTLC to = the result of CC.

Upvotes: 1

Views: 61

Answers (1)

Mik
Mik

Reputation: 2103

If IsEmpty(Cells(1, CC + 1).Value) = False Then
    CC = CC + 1
    FTColSize (CC) ' this is your problem 
    ' change to FTColSize =FTColSize (CC)
Else
    FTColSize = CC
    Exit Function ' you do not need this string
End If

When you call the last recursion you get a right value, but the parent recursion overwrites the value. You can see it going step by step while debbuging.

Upvotes: 1

Related Questions