Prompt User for Number, then pass to another Sub procedure.

[zero269]

Hello,

I'm trying to figure out a way to prompt a user to input a number and to be able to use that input with a different sub procedure.
My reasoning is that I want to reuse this User Prompt in different scenarios instead of baking it into several sub procedures.

For example, I have a sub procedure that prompts me to enter a number which is then used to Insert Table Rows. I want to split the two tasks:

1. Get Input (possibly a Function?)
2. Insert Rows

Here's what I'm currently using to perform both tasks in a single sub procedure:
Any advice would be greatly appreciated.

Sub InsertRows()
'Prompt User for number of rows to insert
    Dim iPrompt As Integer
    iPrompt = InputBox("Enter number of rows to insert", "Insert Rows at end of Table", 1)

    If iPrompt < 1 Then
        Exit Sub
    End If

' Loop Insert Rows
    Dim iCounter As Integer
    iCounter = 0
    Do While iCounter < iPrompt  'Inner Loop
        ActiveSheet.ListObjects(1).ListRows.Add 'Using Table Index
        iCounter = iCounter + 1 ' Increment iCounter
    Loop

' Select first new row
    ActiveSheet.ListObjects(1).TotalsRowRange(1).Select 'Select 1st column in totals row
    ActiveCell.Offset(-iPrompt).Select 'Offsets to select first new row
End Sub

My failed attempt at a function:
I'm not even sure if a function here is the right approach.

Function GetNum(iPrompt As Integer)
    MsgBox iPrompt
End Function

 

[Micron]

The difference between a sub and a function is 2-fold:
- a function can return a value, a sub cannot
- a function is the only procedure that you can call from outside the module it's contained in (e.g. from a control, sheet cell, ribbon)
Both can receive arguments.
If you want to be able to call a function from anywhere it has to be written in a standard module. Depending on how versatile you want it to be, perhaps

Function getInput() As Variant 'Variant will avoid errors if user cancels input box

getInput = InputBox("Enter number of rows to insert", "Insert Rows at end of Table", 1)
If getInput = "" Then getInput = 0

End Function

The call might be as

Sub InsertRows()
Dim i As Integer, n As Integer

i = getInput
If i > 0 Then
   For n = 1 To i
      ActiveSheet.ListObjects(1).ListRows.Add 'Using Table Index
   Next
End If
'more code
Exit Sub

What the calling code won't do as written is handle text inputs where numbers are expected but that's probably not necessary for your users. However, the function will return text, numbers or null because its return type is a Variant.

 

[johnnyL]

- a function is the only procedure that you can call from outside the module it's contained in (e.g. from a control, sheet cell, ribbon)

Are you sure about that?

 

[johnnyL]

Here is a POC (ProofOfConcept):

Insert a module with the following code:

Sub Module1Sub()
'
'Prompt User for number of rows to insert
    Dim iPrompt As Integer
'
    iPrompt = InputBox("Enter number of rows to insert", "Insert Rows at end of Table", 1)
'
    If iPrompt < 1 Then
        Exit Sub
    End If
'
    Call Module2.Module2Sub(iPrompt)
End Sub

Then insert a second module with the following code:

Sub Module2Sub(RowsToInsert As Integer)
'
    MsgBox "The user entered " & RowsToInsert & " in the other module."
'
End Sub

Then execute the code from the first module.

You should see that the variable was passed from the first module to the second module to be handled as desired.

 

[Micron]

That is not what I meant but I see that it's not worded very well. However, in my meager defense I did provide examples of my intent. You're calling from one procedure in one module to another in another module which I have to admit fits what I said you can't do but it doesn't fit the examples.

 

[zero269]

The difference between a sub and a function is 2-fold:
- a function can return a value, a sub cannot
- a function is the only procedure that you can call from outside the module it's contained in (e.g. from a control, sheet cell, ribbon)
Both can receive arguments.
If you want to be able to call a function from anywhere it has to be written in a standard module. Depending on how versatile you want it to be, perhaps

[/QUOTE]
'Prompt User for number of rows to insert
[QUOTE="Micron, post: 6030971, member: 328737"]
Function getInput() As Variant 'Variant will avoid errors if user cancels input box


getInput = InputBox("Enter number of rows to insert", "Insert Rows at end of Table", 1)
If getInput = "" Then getInput = 0

End Function

The call might be as

Sub InsertRows()
Dim i As Integer, n As Integer

i = getInput
If i > 0 Then
   For n = 1 To i
      ActiveSheet.ListObjects(1).ListRows.Add 'Using Table Index
   Next
End If
'more code
Exit Sub

What the calling code won't do as written is handle text inputs where numbers are expected but that's probably not necessary for your users. However, the function will return text, numbers or null because its return type is a Variant.

Hi Micron,

I was able to implement your recommendation successfully.
Here's what I'm using to get the Users Input and then using that value to insert the correct number of Table Rows:

'Prompt User for number of rows to insert
'https://www.mrexcel.com/board/threads/prompt-user-for-number-then-pass-to-another-sub-procedure.1231922/post-6030971
Function getNum() As Variant 'Variant to avoid errors if input cancelled

    getNum = InputBox("Enter number of rows to insert", _
        "Insert Rows at end of Table", 1)
    If getNum = "" Then getNum = 0

End Function

'Insert New Rows to end of Table
Sub InsertTableRows()
    Application.ScreenUpdating = False
    
    Dim iRows As Integer, iCounter As Integer
    iRows = getNum ' getNum Function

    ' Loop Insert Rows
    If iRows > 0 Then
       For iCounter = 1 To iRows
          ActiveSheet.ListObjects(1).ListRows.Add 'Using Table Index
       Next
    End If

    ' Select first new row
    ActiveSheet.ListObjects(1).TotalsRowRange(1).Select
    ActiveCell.Offset(-iRows).Select

    Application.ScreenUpdating = True
End Sub

Thank you very much. This works perfectly.
Once I get a better understanding on the various VBA Procedures, I will look to recreating all of this to allow for a single Input and then use various other Sub procedures to use that input for a variety of uses.
Thanks again.

 

[zero269]

Then execute the code from the first module.

You should see that the variable was passed from the first module to the second module to be handled as desired.

Hi Johnny,

This definitely works, too. However, in my case, my goal is to completely separate the Input from the Action. This way I can tailor various Actions that can all call on that single Input function (value). Later I plan to use that Input with other Actions using that Input value.

Just one minor typo though...

From

Call Module2.Module2Sub(iPrompt)

to

Call Module2Sub(iPrompt)

Thank you,

 

[johnnyL]

1) It wasn't a typo. I put it like that to help demonstrate where the code was being called from. The way I typed it works perfectly fine. If you want an even shorter way of that line of code, you could just use:

Module2Sub iPrompt

As far as "separate Input from the Action" ...
That is what I have done. Your 'iPrompt ' variable can be passed to as many subs as you want.

Another POC example for you ...

In Module 1, paste the following:

Sub Module1Sub()
'
'Prompt User for number of rows to insert
    Dim iPrompt As Integer
'
    iPrompt = InputBox("Enter number of rows to insert", "Insert Rows at end of Table", 1)
'
    If iPrompt < 1 Then
        Exit Sub
    End If
'
    Module2Sub iPrompt
'
    Module3Sub iPrompt
End Sub

In Module2, paste the following:

Sub Module2Sub(RowsToInsert As Integer)
'
' Do some code that uses the 'iPrompt' variable
'
    MsgBox "Result of Module2 code = The user entered " & RowsToInsert & " in the first module."
End Sub

In Module 3, paste the following:

Sub Module3Sub(RowsToInsert As Integer)
'
' Do some code that uses the 'iPrompt' variable
'
    MsgBox "Result of Module3 code = The user entered " & RowsToInsert & " in the first module."
End Sub

When you execute the first module, you should be able to see that the 'iPrompt' value is being passed to both of the other two modules.

 

[johnnyL]

If you want to keep the variable 'iPrompt' the same in the other modules you could use for module 2:

Sub Module2Sub(iPrompt As Integer)
'
' Do some code that uses the 'iPrompt' variable
'
    MsgBox "Result of Module2 code = The user entered " & iPrompt & " in the first module."
End Sub

And for Module3:

Sub Module3Sub(iPrompt As Integer)
'
' Do some code that uses the 'iPrompt' variable
'
    MsgBox "Result of Module3 code = The user entered " & iPrompt & " in the first module."
End Sub

 

[zero269]

Hi @johnnyL

Thanks for the follow up and alternative methods to use. They look promising at first glance, but I'll need to some time to experiment with them; hopefully this evening or tomorrow.
Right now, I'm struggling with those If-Then-Else Statements that seem to be a nemesis for me... and I'm determined to figure it out. I'm not good at programming, and VBA is a bit confusing, although I have to admit I have come much further since joining this amazing forum.

Thanks for your time and effort in helping me. It's very much appreciated.

Once example that I literally just improved on my own...

From this:

Sub EnterQuizNumber()
    Range("A1").Copy
    ActiveCell.PasteSpecial xlValues
    Application.CutCopyMode = False
    ActiveCell.Offset(1).Select 'Select next cell below
End Sub

To this:

Sub EnterQuizNumber()
    ActiveCell.Value = Range("A1")
    ActiveCell.Offset(1).Select 'Select next cell below
End Sub

Baby steps...