Refer to function variable?

[robfo0]

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

 

[Juan Pablo González]

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

 

[robfo0]

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

 

[Juan Pablo González]

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

 

[robfo0]

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

 

[Juan Pablo González]

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

 

[robfo0]

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?