Functions Within Functions...

I'm having trouble with functions and how to either get them to work together or cancel all together...I'll show you what I mean.

Here's an example of code I have written that works fairly well within itself: [VBA]Public Function Deal_IDs()
On Error Resume Next
Worksheets("Sheet1").Activate
Dim DealIDs As Range
Set DealIDs = Application.InputBox("With your mouse, highlight the column for the Deal IDs / Sequence #s:", "Deal IDs", _
"F:F", Type:=8)
If DealIDs = vbCancel Then
Exit Function
Else
If DealIDs Is Nothing Then
Answer = MsgBox("A valid selection hasn't been made for the Deal IDs / Sequence #s. Try again?", vbOKCancel + vbQuestion)
If Answer = vbCancel Then 'No retry
Exit Function
Else 'retry
Run "Deal_IDs"
End If
End If
End If
End Function

Public Function Request_End_Date()
On Error Resume Next
Worksheets("Sheet1").Activate
Dim RequestEndDate As Range
Set RequestEndDate = Application.InputBox("Highlight the column for the Requested Expiration Date:", _
"Requested Expiration Date of Current Deal", "A:A", Type:=8)
If RequestEndDate = vbCancel Then
Exit Function
End If

If RequestEndDate Is Nothing Then
RequestEndDate = MsgBox("A valid selection hasn't been made for the Requested Expiration Date. Try again?", vbOKCancel + vbQuestion)
If Answer = vbCancel Then 'No retry
Exit Function
Else 'retry
Run "Request_End_Date"
End If
End If
End Function[/VBA]

I'm having 2 problems:

1) If the user selection is invalid, I'm not getting the msgbox I'm wanting...excel is popping it's standard pop (which nonetheless gets the job done but I want my error message to appear).
2) How do I call the 2nd function within the 1st function? If the user selects cancel at any time, I want the entire macro to stop but if the user does the right selections, I want it to continue until there's nothing left.

Any help would be appreciated..I've been sitting here for hours trying to figure it out to no avail. :dunno

Try
[VBA] Dim uiRange as Range

On Error Resume Next
uiRange = Application.InputBox("Select a Range", type:=8)
On Error Goto 0

if uiRange Is Nothing Then
MsgBox "cancel pressed"
Else
MsgBox uiRange.Address & " selected"
End If[/VBA]

Quote:

---

Originally Posted by PianoMan5

I'm having 2 problems:

1) If the user selection is invalid, I'm not getting the msgbox I'm wanting...excel is popping it's standard pop (which nonetheless gets the job done but I want my error message to appear).
2) How do I call the 2nd function within the 1st function? If the user selects cancel at any time, I want the entire macro to stop but if the user does the right selections, I want it to continue until there's nothing left.

Any help would be appreciated..I've been sitting here for hours trying to figure it out to no avail. :dunno

---

1. I may be mis-reading, but if you mean to avoid the alert that comes up when the user supplies a bad or missing range address and then clicks the <OK> button, you could have .DisplayAlerts turned off - but in my opion, this would be of no help. See, at the time that the user clicks the <OK>, <Cancel>, or the 'X' button in the titlebar, we are still actually running inside of the application supplied function .InputBox. Thus, until the dialog is dismissed, we are not going to change the built-in call to the alert to a call to our own message box. Does that seem sensible?
2. At least for my pea brain, I think we would need to know what is being done in each function (presuming a valid range is selected), before thinking about how/where we'd be calling a second function.

Here is a stab at just calling the first function recursively:
[VBA]
Option Explicit

Sub StartItUp()
'// If we are going to do something first and only once, that is, something that //
'// does not need done repeatedly, maybe do this in a calling procedure. //
Worksheets("Sheet1").Activate
Call Deal_IDs
End Sub

Public Function Deal_IDs()
Dim rngDealIDs As Range
'// Ignore errors ONLY for as long as necessary. //
On Error Resume Next
Set rngDealIDs = _
Application.InputBox( _
"With your mouse, highlight the column for the Deal IDs / Sequence #s:", _
"Deal IDs", _
"F:F", _
Type:=8)
On Error GoTo 0

If Not rngDealIDs Is Nothing Then
Call Deal_IDs
Else
If MsgBox("A valid selection hasn't been made for the Deal IDs / Sequence #s. Try again?", _
vbOKCancel + vbQuestion + vbDefaultButton2) = vbOK Then
Call Deal_IDs
End If
End If
End Function[/VBA]

Hope that helps,

Mark

GTO / Mike -- Thanks for the replies!

However, that isn't solving my problem at least for the 1st point (my error message and not Excel)...being the default error box from Excel is doing what I want anyways, I don't have a problem with that so I remove the redundance in code.

To your point in understanding my vision, GTO, I want the function to assign a given variable a set range I'm going to use later on in a sub to complete a vlookup against. I've got 5 different functions which are similar (asking the user to provide the cell/column locations) but I also want the entire sub to cancel once the user selects cancel at any time.

I can run a separate function to have the 5 functions within but I can't figure out how to either have each individual function or the one "big" function to understand the user has selected cancel on the messagebox and to stop the entire thing.

You could have the UDF return a "user canceled" value. Nothing is a common value for that when a range is expected.
I like to write UDF's that mimic what an InputBox returns when Cancel is pressed, depending on the data type expected.

[VBA]Sub Main()
Dim EnteredRange As Range
Dim uiDate As Date

Set EnteredRange = mySelectionFunction()
If EnteredRange Is Nothing Then Exit Sub: Rem cancel pressed

uiDate = MySelectedDate()
If uiDate = 0 Then Exit Sub: Rem cancel pressed

EnteredRange.Value = uiDate

End Sub

Function mySelectionFunction() As Range
Dim aRange As Range, bRange As Range

Set aRange = myRangeFunction()
If Not (aRange Is Nothing) Then
Set bRange = myRangeFunction()
If Not (bRange Is Nothing) Then
Set mySelectionFunction = Range(aRange, bRange)
End If
End If
End Function

Function MySelectedDate() As Date
Dim uiCell As Range

Set uiCell = myRangeFunction()
If Not uiCell Is Nothing Then
If IsDate(uiCell.Cells(1, 1).Value) Then
MySelectedDate = CDate(uiCell.Cells(1, 1).Value)
End If
End If

End Function

Function myRangeFunction() As Range
On Error Resume Next
Set myRangeFunction = Application.InputBox("Select a range", Type:=8)
On Error GoTo 0
End Function[/VBA]

I am not going to add anything to the question asked, that seems to be in hand, but I am going to point something out that I feel is good coding practice.

As well as addressing the question in hand, both Mike and GTO adopted a particular coding style but did not draw any attention to it. I am drawing attention to it as I think it is important.

If you look at your code, you have code that is similar to this pseudo-code

[VBA]If some_condition_met Then
Exit Function
Else
do-what_is_really_required
'etc
End If[/VBA]

But look at how Mike and GTo wrote their code, it is along the lines of

[VBA]If Not some_condition_met Then
do-what_is_really_required
'etc
End If[/VBA]

What this does is to avoid the code being peppered with Exit Function statements, or in some cases 'do nothing' comment lines. I feel that Mike and GTOs code style makes it more maintainable. Maintenance of code is the second most important consideration when developing (the first is to make it work of course), so it is important.