How to change the result of a function?!

Posted on 2007-07-25
Medium Priority
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

ID: 19568570
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

ID: 19568752
In VB6 ?
LVL 86

Accepted Solution

Mike Tomlinson earned 400 total points
ID: 19568930
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 38

Assisted Solution

PaulHews earned 400 total points
ID: 19569327
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 86

Expert Comment

by:Mike Tomlinson
ID: 19569365
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...

Assisted Solution

tonym001 earned 400 total points
ID: 19583199
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

ID: 19583385

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

ID: 19648624

Thanks to all.


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

807 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