setup
i'm writing a macro that asks the user to specify the index numbers of the tables to be worked on. then i realized i've come across a couple of times this scenario of requiring user-defined integers. so, why not make a function for it?
solution
the plan
- pop up a prompt asking for input with instructions and example (e.g. 1 22 3-5).
- tell if the input is good. if no good, prompt reason and ask to run macro again.
- if good, split by seperator (space, i.e. " ").
- for each splitted itm, trim and add if it's a number or add numbers within range if it's a range.
programming language & module(s)
- VBA
file preps
none.
variables to customize
prompt. input prompt with an example of how to type in the index numbers.inputErrPrompt. a message that shows up on the pop-up box when characters other than numbers, spaces and the minus sign (i.e. "-") have been typed in.
the script
fGetUserInpIdx
| Function fGetUserInpIdx() As Collection | |
| Dim rtn As Collection | |
| Dim ret As Object | |
| Dim prompt, inputErrPrompt As String | |
| Dim startIdx, endIdx As Long | |
| prompt = "please type in the index numbers. seperate by space (i.e. ' ')." & vbCr & _ | |
| "'1 22 3-5' for nos. 1, 3, 4, 5, and 22" | |
| inp = InputBox(prompt) | |
| If Len(Trim(inp)) > 0 Then | |
| Set ret = CreateObject("VBScript.RegExp") | |
| ret.pattern = "[^\d- ]" | |
| If ret.test(inp) Then | |
| inputErrPrompt = "it appears something other than numbers, spaces, and '-' were typed in." & vbCr & _ | |
| "please run the macro again." | |
| MsgBox inputErrPrompt | |
| Else | |
| splitted = Split(inp, " ") | |
| Set rtn = New Collection | |
| ret.pattern = "(\d+) *- *(\d+)" | |
| For i = LBound(splitted) To UBound(splitted) | |
| itm = Trim(splitted(i)) | |
| If ret.test(itm) Then | |
| startIdx = ret.Execute(itm)(0).submatches(0) | |
| endIdx = ret.Execute(itm)(0).submatches(1) | |
| For n = startIdx To endIdx | |
| rtn.Add n | |
| Next | |
| Else | |
| rtn.Add CInt(itm) | |
| End If | |
| Next | |
| End If | |
| End If | |
| Set fGetUserInpIdx = rtn | |
| End Function |
output
a Collection object to be passed to the macro that calls this function.
note to self
- space was used as separator because others, such as period, comma, etc. risk the user unknowingly entering the counterpart punctuations under CN input method. space is space irrespective of input method, thereby minimizing troubleshooting hassle.