I'm not great with RegEx.  I need to retrieve the issue number from the long title of a comic book that includes its name and sometimes the artist's name.  Usually the issue number is the last number in the string, but not always.  Here are 6 examples that capture the range of variations I'm looking at:
STAR WARS: DOCTOR APHRA 32 CHRIS SPROUSE RETURN OF THE JEDI 40TH ANNIVERSARY VARIANT
DEADPOOL 7
X-23: DEADLY REGENESIS 3 GERALD PAREL VARIANT
SPIDER-MAN 2099: DARK GENESIS 5
THE GODFORSAKEN 99 OF KRONOS 2 KEN GRAGGINS VARIANT
Teenage Mutant Ninja Turtles: Saturday Morning Adventures (2023-) #1 Variant RI (10) (Dooney)

I'm using VBA and this is my current function:
Function ExtractText(c As Range) As String
    Dim rgx As RegExp
    Dim match As match
    Dim mc As MatchCollection
    Dim sComicNo As String, sPattern As Variant
    Dim lPos As Long, x As Long

    sComicNo = ""
    sPattern = Array(" [0-9] ", " #[0-9] ", " [0-9][0-9] ", " #[0-9][0-9] ", " [0-9][0-9]", " #[0-9][0-9]", " [0-9]", " #[0-9]")
    lPos = 0
    
    Set rgx = New RegExp
    
    On Error GoTo ErrHandler
    
    Do While sComicNo = ""
        
        With rgx
            .Pattern = sPattern(x)
            .Global = True
        
            If .Test(c.Value) Then
                Set mc = .Execute(c.Value)
                
                If mc.Count > 0 Then
                    Set match = mc.Item(mc.Count - 1)
                Else
                    ExtractText = ""
                End If
                
                lPos = match.FirstIndex
                sComicNo = WorksheetFunction.Trim(match.Value) & "|" & lPos
            Else
                sComicNo = ""
            End If
            
            x = x + 1
            
            If x > 8 Then
                ExtractText = sComicNo
                Exit Function
            End If
        
        End With
        
    Loop
    
    ExtractText = sComicNo
    
ErrHandler:

    Exit Function
    
End Function

This pattern matches all my examples except the Spider-Man 2099, but there are other possible variations I'm overlooking.  It is also retrieving the position of the match for a separate purpose.  I'm trying to be as restrictive as possible by using patterns in a sequence that will retrieve very specific cases and gradually work up from there.

Retrieve issue number from string for comic
I don't know VBA, but by trying to comprehend the code I think your regex can be simplified to this:
\s      # Match a whitespace,
#?      # an optional '#', then
\d\d?   # 1 or 2 digits, followed by
\b      # a word boundary (prevents numbers with 3+ digits from being matched).

Try it on regex101.com.
Alternatively, you can use a lookbehind to skip the trimming part:
(?<=\s)#?\d\d?\b

...in which (?<=\s) means "match something preceded by a whitespace".
Try it on regex101.com.
