How to change the result of a function?!

Posted on 2007-07-25
Last Modified: 2010-08-05

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.

Question by:akohan
    LVL 13

    Expert Comment

    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.
    LVL 48

    Expert Comment

    In VB6 ?
    LVL 85

    Accepted Solution

    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
    LVL 38

    Assisted Solution

    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
    LVL 85

    Expert Comment

    by:Mike Tomlinson
    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...
    LVL 2

    Assisted Solution

    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

    Author Comment


    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.


    Author Comment


    Thanks to all.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now