How to change the result of a function?!

Hello group,

I'm writing a function that reterives number of avaiable macros in an Excel workbook. This works fine but sometimes I need to have this functions the number of avaiable macros in a Workbook and sometimes I want it to return the list or namesof them.

How can I write a functions that its return value could switch from string to integer?

Your help will be appreciated.

Who is Participating?
Mike TomlinsonMiddle School Assistant TeacherCommented:
Sounds like a bad idea to me...

But is this the type of thing you want?

Option Explicit

Private Sub Command1_Click()
    Dim i As Integer
    Dim count As Integer
    Dim names() As String
    count = ThisIsABadIdea(True)
    names = ThisIsABadIdea(False)
    For i = 0 To count - 1
        Debug.Print i, names(i)
End Sub

Private Function ThisIsABadIdea(ByVal getCount As Boolean) As Variant
    Dim names As String
    names = "a|b|c" ' simulated macro listing...
    If getCount Then
        ThisIsABadIdea = UBound(Split(names, "|")) + 1
        ThisIsABadIdea = Split(names, "|")
    End If
End Function
You can't if the return variable was already declared.

If you don't use OPTION EXPLICIT to force you to declare your variables, then the type of variable returned will be determined by the contents of the variable.

So if you return an alphanumeric value, it will be string, but if you return a whole number it will be an integer.
Jorge PaulinoIT Pro/DeveloperCommented:
In VB6 ?
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

I agree it's a bad idea.  Multipurpose functions, even when well documented, could be used wrongly causing interesting errors.  This is especially bad after a couple of years when you've forgotten how it all works.  

The cleanest solution would be two separate functions.  One to return a count and one to return a list.  There should be fewer mistakes when calling these.  

I think that the next cleanest solution is to use ByRef parameters to pass back different typed values.  Using an optional parameter allows you some flexibility in its use.  Note the content of the GetMacros sub needs to be updated with your code:

Option Explicit

Private Sub Command1_Click()
    Dim i As Integer
    Dim intCount As Integer
    GetMacros intCount
    MsgBox intCount
    Dim colNames As Collection
    Set colNames = New Collection
    GetMacros intCount, colNames
    For i = 1 To intCount
        Debug.Print colNames(i)
End Sub

Private Sub GetMacros(ByRef intCount As Integer, Optional ByRef colNames As Collection = Nothing)
    Dim i As Integer
    intCount = 4  'Whatever code you need here
    If Not colNames Is Nothing Then
        For i = 0 To 3
            colNames.Add "Macro" & i
        Next i
    End If
End Sub
Mike TomlinsonMiddle School Assistant TeacherCommented:
akohan...Why not just always return an array of string and then calculate the count from that array?

Or you could return a Collection object with the names in it...
You have provided very little information so I am guessing here

I assume you are trying to do this from VB as opposed to Microsoft Visual Basic within Excel.
If this the case, I would suggest that you adopt an easy solution as opposed to the correct solution.
The correct solution would involve extensive use of windows APIs and woulld take weeks to implement and test. The easy solution is to write the values to a text file that you control and read accordingly

If however the code is in Excel
IYou need to call the function using byref
For Example

Private Sub CountMacos()
Dim MyMacroNames() As String
Dim intNumberOfMacros As Integer
Dim intCount As Integer

PopulateArray MyMacroNames

intNumberOfMacros = UBound(MyMacroNames) + 1

For intCount = 0 To UBound(MyMacroNames) - 1
    'Do Whatever in this case print in immediate wnidow
    Debug.Print MyMacroNames(intCount)

End Sub

Private Sub PopulateArray(ByRef strMacroNames() As String)
Dim intCount As Integer

'Change this to enumerate the macros
For intCount = 0 To NumberofMacros
    ReDim Preserve strMacroNames(intCount)
    strMacroNames(intCount) = intCount

End Sub
if this simplistic its meant to be - I have spent far too many hours trying to do the impossible
akohanAuthor Commented:

Sorry for later response, have been busy. I know guys you are right it is a bad idea however, I'm going to check all the answers and give you a feedback soon.

As you mentioned, right I believe retruning an array or collection is what we do in other languages as well.

I truly appreciate your help and will get back to you soon.

akohanAuthor Commented:

Thanks to all.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.