VBA Function returning #VALUE when called within excel

Question

Below is a function that is supposed to return a random value between 1 and 10 that is not already in column A. It works fine in terms of finding the random value and exiting the loop but in excel when called using =Ang() the function returns #Value! as below.

Function Ang()
  i = 0
  Do
   i = Application.WorksheetFunction.RandBetween(1, 10)
   Ang = i
   MsgBox Ang
  Loop While Application.WorksheetFunction.IfNa(Application.WorksheetFunction.Match(i, Worksheets("Sheet2").Range("A:A"), 0), 0)
End Function

@PatrickHonorez While that is a sound advice that I second, it is not the source of the problem. — GSerg, Dec 12 '17 at 15:00
Possible duplicate of [Excel VBA: Can't get a match, error “Unable to get the Match property of the WorksheetFunction class”](https://stackoverflow.com/q/17751443/11683) — GSerg, Dec 12 '17 at 15:02
Thanks for the pointers. The issue was indeed the Match Function. — Manmeet Singh, Dec 12 '17 at 15:32

Scott Craner · Accepted Answer · 2017-12-12T15:16:41.953

0

The issue is WorksheetFunction.Match will stop the code with an error if it is not found.

Use Application.Match instead:

Function Ang() as Long
  Dim i as Long
  i = 0
  Do
   i = Application.WorksheetFunction.RandBetween(1, 10)
   Ang = i
   MsgBox Ang
  Loop While Not IsError(Application.Match(i, Worksheets("Sheet2").Range("A:A"), 0))
End Function

Or

Function Ang()
  i = 0
  Do

   i = Application.WorksheetFunction.RandBetween(1, 10)
   Ang = i
   MsgBox Ang
  Loop While Application.WorksheetFunction.CountIf(Worksheets("Sheet2").Range("A:A"), i)
End Function

edited Dec 12 '17 at 15:16

answered Dec 12 '17 at 15:07

Scott Craner

148,073
10
49
81

Super man. That was quick. Many thanks. How do we figure out that Match function will stop the code with an error? Is there a manual? – Manmeet Singh Dec 12 '17 at 15:29

score 0 · Answer 2 · answered Dec 12 '17 at 15:24

Rather than hard-wiring in the range of numbers to avoid, why not make it an argument to the function?

Function NewRandom(R As Range, a As Long, b As Long) As Variant
    'returns a random number in a,b which isn't in range R
    Dim i As Long, k As Long, rand As Long
    Dim c As Range
    Dim avoid As Variant
    Dim count As Long

    ReDim avoid(a To b) As Boolean
    For Each c In R.Cells
        k = c.Value
        If a <= k And k <= b Then
            avoid(k) = True
            count = count + 1
        End If
    Next c

    If count = b - a + 1 Then 'error condition!
        NewRandom = CVErr(xlErrValue)
    Else
        Do
            rand = Application.WorksheetFunction.RandBetween(a, b)
        Loop While avoid(rand)
        NewRandom = rand
    End If
End Function

Used like:

The function guards against an infinite loop. If in B1 I used =NewRandom(A1:A7,1,5) then the error #VALUE! would be returned. The code assumes that the range from a to b is not so large as to be a significant memory drain. If it is, then the array avoid can be replaced by a dictionary.

Nice ! Thanks for this. – Manmeet Singh Dec 13 '17 at 13:50 — Manmeet Singh, Dec 13 '17 at 13:50

VBA Function returning #VALUE when called within excel

2 Answers2