Range saving the result of a validity check possible?

Question

I am writing several UDFs which all take a Range as input. As I need to check whether or not the user input is valid I have built another Function to check things like IsNumeric or IsEmpty and so on. Everytime a check has finished it, there is a MsgBox if there have been some issues detected. I want to keep this MsgBox but as there are different UDFs which will be used on the same Range I wanted to ask if I can reduce the amount of Pop-ups? If there is a way to "save" the fact that another Function has already compleated the validity check for this Range? There is no way to know in advance how many times any of the functions are going to be called.

My code is too long and has other issues as well but this is the basic idea:

Public Const maxn As Long = 10

Function Check(myRange As Range, p As Long, n As Long) As Boolean
    Dim i As Long, k As Long, x As Long
    Dim emptyCell As String, noNum As String, emptyRow As String 
    Dim empty(1 To maxn) As Long 

    For i = 1 To p
        For k = 1 To n
            If IsEmpty(myRange.Cells(i, k).Value) Then
                x = x + 1
                empty(k) = 1
                If x = n Then
                    emptyRow = emptyRow & vbLf & CStr(myRange.Row + i - 1)
                End If
            ElseIf Not IsNumeric(myRange.Cells(i, k).Value) Then
                noNum = noNum & vbLf & ColNo2ColLet(myRange.Column + k - 1) & CStr(myRange.Row + i - 1)
            End If
            If k = n And x > 0 And x <> n Then
                For x = 1 To n
                    If empty(x) = 1 Then emptyCell = emptyCell & vbLf & ColNo2ColLet(myRange.Column + x - 1) & CStr(myRange.Row + i - 1)
                Next x
            End If
        Next k
    Next i
    
    If emptyRow <> "" Then
        emptyRow = "Following rows are empty and will not be considered:" & emptyRow
        MsgBox (emptyRow)
    End If
    If emptyCell <> "" Then
        emptyCell = "Following cells are empty and will not be considered:" & emptyCell
        MsgBox (emptyCell)
    End If
    If noNum <> "" Then
        noNum = "Following cells contain nonnumeric values:" & noNum
        MsgBox (noNum)
        Check = CVErr(xlErrValue)
        Exit Function
    End If
    Check = True
End Function

Function ColNo2ColLet(x as Long) as String
'returns the Letters corresponding to the Column number provided by myRange.Column
End Function

Function sr(myRange As Range) as Double
    ' p, n get defined and checked
    Call Check(myRange, p, n)
    ' other calculations
End Function

Function Q(myRange As Range) as Double
    ' p, n get defined and checked
    Call Check(myRange, p, n)
    ' other calculations
End Function

Do you call `Check` as a formula in the sheet? Then I highly recommend not to use `MsgBox` that must be so confusing having all these popups on every recalculation of the sheet. A UDF should only return a value or an array of values (if you want to return more than one value). — Pᴇʜ, Jul 15 '21 at 14:19
No I only call `Check` from the other formulas.. so I only call it in `Q` and `sr`. — LostInTheJungle, Jul 15 '21 at 14:22
But are `Q` and `sr` used as formulas in a sheet then? That would still trigger all the boxes on recalculation. That will get horrendously messy. — Pᴇʜ, Jul 15 '21 at 14:24
yes. I know but i need it because I need to know when a row is empty and i would like to avoid going through the whole table trying to find out where the typo of 2..5 instead of 2.5 lies. — LostInTheJungle, Jul 15 '21 at 14:29
I think you ask for problem X here while your actual problem is Y. See [What is the X/Y-Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). And rethink if you ask the right question here. — Pᴇʜ, Jul 15 '21 at 14:30

Siddharth Rout · Accepted Answer · 2021-07-15T14:27:36.487

This is what I do. I declare one variable to store messages and then "collect" messages in that. Finally I just show 1 message box in the end with all the messages in one go.

Option Explicit

Dim msg As String

Sub Sample()
    Dim Ret As Variant
    msg = ""

    Ret = SomeFunctionA(1)
    Ret = SomeFunctionB(1)
    
    If msg <> "" Then MsgBox msg
End Sub

Function SomeFunctionA(x As Long) As String
    '
    '~~> Some code
    '
    If msg = "" Then msg = "Error A" Else msg = msg & vbNewLine & "Error A"
End Function

Function SomeFunctionB(x As Long) As String
    '
    '~~> Some code
    ' 
    If msg = "" Then msg = "Error B" Else msg = msg & vbNewLine & "Error B"
End Function

Alternative way if you do not want to type If msg = "" Then msg = ... again and again

Option Explicit

Dim msg As String

Sub Sample()
    Dim Ret As Variant
    msg = ""
    
    Ret = SomeFunctionA(1)
    Ret = SomeFunctionB(1)
    
    If msg <> "" Then MsgBox msg
End Sub

Function SomeFunctionA(x As Long) As String
    '
    '~~> Some code
    '
    StoreMessage "Error A"
End Function

Function SomeFunctionB(x As Long) As String
    '
    '~~> Some code
    '
    StoreMessage "Error B"
End Function

Private Sub StoreMessage(s As String)
    If msg = "" Then msg = s Else msg = msg & vbNewLine & s
End Sub

sounds good but in this case if I have a empty cell in A4 and B15 and In the worksheet I call `sr("A1:C31")` and `Q("A1:C31")` then I'll get the message : A4, B15, A4, B15.. is there a way to only get A4, B15? — LostInTheJungle, Jul 15 '21 at 14:35
If you are getting repitative messages then Instead of a string use a [unique collection](https://stackoverflow.com/questions/18799590/avoid-duplicate-values-in-collection). — Siddharth Rout, Jul 15 '21 at 14:50

Range saving the result of a validity check possible?

1 Answers1