Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Refer to function variable?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is it possible to refer to variables in a function from a macro? for example, i have this code in my loop macro:
    Cells(i + 4, 1) = FilenameOnly(.FoundFiles(i))

    which refers to the FilenameOnly function, but if for instance i had another variable in this function, could i refer to it somehow or do i need to create a separate function to refer to it?

    Thanks

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not clear with what you want... can you explain a little further ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok, here is the function I combined from from different functions Ive gathered from everyone on the board (thanks )

    Function FilenameOnly(strPath As String) As String
    Dim lngCharCounter As Long
    Dim Loan_Officer_Dir, Date_Dir As String

    'First establish the filename and extenstion part
    For lngCharCounter = Len(strPath) To 1 Step -1
    If Mid(strPath, lngCharCounter, 1) = "" Then
    FilenameOnly = Right(strPath, Len(strPath) - lngCharCounter)
    'Remove the extension
    FilenameOnly = Left(FilenameOnly, InStr(1, FilenameOnly, ".") - 1)
    Exit For
    End If
    Next lngCharCounter
    'set Pos1 value
    For i = Len(MyString) To 0 Step -1
    If Mid(MyString, i, 1) = "" Then
    Pos1 = i - 1
    Exit For
    End If
    Next i

    'set Pos2 value
    For j = Pos1 To 0 Step -1
    If Mid(MyString, j, 1) = "" Then
    Pos2 = j - 1
    Exit For
    End If
    Next j

    'set Pos3 value
    For k = Pos2 To 0 Step -1
    If Mid(MyString, k, 1) = "" Then
    Pos3 = k + 1
    Exit For
    End If
    Next k
    Loan_Officer_Dir = Mid(MyString, Pos2, 1 + Pos1 - Pos2)
    Date_Dir = Mid(MyString, Pos3, 1 + Pos2 - Pos3)
    End Function


    Then I refer to the FilenameOnly function in my macro with:

    Cells(i + 4, 1) = FilenameOnly(.FoundFiles(i))

    i tried referring to the Loan_Officer_Dir variable in my function with:

    Cells(i + 4, 2) = Loan_Officer_Dir(.FoundFiles(i))

    but that didnt work. I used to have a total different function for Loan_Officer_Dir, but i noticed that the functions were very similar so im trying to consolidate on the code and put these 3 functions into 1. I just dont know how to refer to them correctly.

    Thanks

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The problem is that the Loan_etc. variable is a private variable in your function. You could use it if you "Dim" it before you declare the function, like this

    Function FilenameOnly(strPath As String) As String
    ...
    End Function
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, Im a little confused, I guess my question is : Is is possible for a function to return more than one variable?

    Here is what i was trying to do. I have the following 3 functions, which are very similar. They take (from a directory in my macro) the filename, and the 2 directories before the file (Loan_Officer_Dir, and Date_Dir). Is it possible to combine these 3 functions into 1? Here are the 3:

    Function FilenameOnly(strPath As String) As String
    Dim lngCharCounter As Long
    'First establish the filename and extenstion part
    For lngCharCounter = Len(strPath) To 1 Step -1
    If Mid(strPath, lngCharCounter, 1) = "" Then
    FilenameOnly = Right(strPath, Len(strPath) - lngCharCounter)
    'Remove the extension
    FilenameOnly = Left(FilenameOnly, InStr(1, FilenameOnly, ".") - 1)
    Exit For
    End If
    Next lngCharCounter
    End Function


    Function Loan_Officer_Dir(MyString)
    Dim Pos1, Pos2, i, j

    'set Pos1 value
    For i = Len(MyString) To 0 Step -1
    If Mid(MyString, i, 1) = "" Then
    Pos1 = i - 1
    Exit For
    End If
    Next i

    'set Pos2 value
    For j = Pos1 To 0 Step -1
    If Mid(MyString, j, 1) = "" Then
    Pos2 = j + 1
    Exit For
    End If
    Next j

    'return value
    Loan_Officer_Dir = Mid(MyString, Pos2, 1 + Pos1 - Pos2)
    End Function


    Function Date_Dir(MyString)
    Dim Pos1, Pos2, Pos3, i, j, k

    'set Pos1 value
    For i = Len(MyString) To 0 Step -1
    If Mid(MyString, i, 1) = "" Then
    Pos1 = i - 1
    Exit For
    End If
    Next i

    'set Pos2 value
    For j = Pos1 To 0 Step -1
    If Mid(MyString, j, 1) = "" Then
    Pos2 = j - 1
    Exit For
    End If
    Next j

    'set Pos3 value
    For k = Pos2 To 0 Step -1
    If Mid(MyString, k, 1) = "" Then
    Pos3 = k + 1
    Exit For
    End If
    Next k

    'return value
    Date_Dir = Mid(MyString, Pos3, 1 + Pos2 - Pos3)
    End Function

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-23 14:50, robfo0 wrote:
    Ok, Im a little confused, I guess my question is : Is is possible for a function to return more than one variable?
    Well, yes. Instead of returning a string only, you'll have to return an Array of 3 elements, thefefor your function will be

    Function MyFunc(..) as Variant
    Dim T(1 to 3) as String

    T(1) = some stuff
    T(2) = Some other stuff
    T(3) = More stuff here !

    MyFunc = T
    End Function
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Stockton, California
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok sorry Juan, thanks for the help, but one more question. How would i refer to this array in my macro in which i call these 3 different variables?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •