VB: UDF - FUNCTION Instability

Davexx

New Member
Joined
Jun 8, 2015
Messages
21
hi, i have 2 or 3 user defined functions that seem to come up with errors intermittently. (for me, i noticed that on making any change to a function called either: ZOR() or ZAND() the error would come up right away / upon calculating a row). thanks in advance.


i can use help with:
- fixing the intermittent errors that popup on the top 2 examples
- making the ISTRUE function to work with a range of cells, eg: A9:C9

----------

the vb used to calculate row:
Code:
ActiveCell.EntireRow.Calculate


i have been forced to use vb for a manual type: hit enter on cell:
Code:
Cells(ActiveCell.row, "R:R").Formula = Cells(ActiveCell.row, "R:R").Formula


i am using winxp pro & excel 2002, upgrade pending but not right now.

ANSWER CHECK:
NOTE: at bottom i have a sample of a code that is stable (QLINK). if reason for stability, the code is divided into 2 parts (as below): the udf & then the rest of the code under area that activates code upon user click:
Private Sub Worksheet_SelectionChange(ByVal Target As RANGE) 'single click


ERRORS:
i am novice at vb & may need a working example / help with discovering what the problem is with this.
i have a couple of samples for udf's that are causing ERRORS:


'GETS NAME ERROR, HIT ENTER ON CELL TO FIX: (note: eg for range of cells possible)
Code:
Function ZOR(ParamArray Refs()) As Boolean    'find zero in OR(cells as:  =IF(ZOR(B9),1,0)  or  =IF(ZOR(B9:D9),1,0)
  Dim i As Integer
  Dim c As RANGE
  On Error Resume Next    'ON ERROR
  For i = LBound(Refs) To UBound(Refs)
    For Each c In Refs(i).Cells
      If Not IsEmpty(c.Value) And c.Value = 0 Then    'ZOR requirements:
        ZOR = True
        Exit Function
      End If
    On Error GoTo 0      'ON ERROR
    Next
  Next
  'ZOR = False  'not for OR, used in AND
End Function


'GETS NAME ERROR, HIT ENTER ON CELL TO FIX: (note: need help with test range of cells)
Code:
Function IsTrue(ByRef x As RANGE) As Boolean   'call as:  IF(ISTRUE(A1),1,0), not ready for a range of cells eg:  A1:B1
    IsTrue = False        'used to simulate test cell:  if isnumber & if >0,  need help making enter range of cells possible
    If x.Value > 0 Then IsTrue = True
    If Not IsNumeric(x.Value) Then IsTrue = False
End Function



'========== SAMPLE THAT IS STABLE: QLINK: used as alternative to hyperlink using: SCROLLROW, no more offsets.
'code in a module:


Code:
Function QLINK(rngToSel As RANGE, strFriendlyText As String) 'add to a module,  see sheet1 qlink
    QLINK = strFriendlyText
End Function    'for:  =QLINK($A$200,"A")  or  =QLINK($AD$200:$AF$200,"Friendly Name")



and the 2nd part within the sheet:


Code:
option explicit
Private Sub Worksheet_SelectionChange(ByVal Target As RANGE)  'single click


With Target     'end with at bottom 
    If .Count > 1 Then 'Exit Sub
      Application.EnableEvents = True           'EVENTS
      Exit Sub
    End If


    Dim strFormula As String, strRefToSel As String   'QLINK  quick link:  single click scroll row to destination for top of view, moving up -or- dn.
    Dim rngToSel As RANGE                             'part of Q LINKXYZ below
    On Error Resume Next
    strFormula = Target.Formula
    
    
    If UCase(Left(strFormula, 6)) = UCase("=QLINK") Then   'call as:  =QLink($A$897,"friendly name")
    'Application.ScreenUpdating = False             'no longer need update off
        strRefToSel = Mid(strFormula, 8)
        strRefToSel = Left(strRefToSel, InStr(strRefToSel, ",") - 1)
        Set rngToSel = RANGE(strRefToSel)      'remove:  Me.  to keep from selecting destination up front & get rid of update off.
        'Set rngToSel = Me.RANGE(strRefToSel)  'orig      'Application.DoubleClick    'no longer need
        
        If Not rngToSel Is Nothing Then         'jump to rng & do scrolling 
            ActiveWindow.ScrollRow = rngToSel.row - 1     '<< SCROLL  yes
        End If
        

    Application.Goto rngToSel   
    End If


end with
end sub


i can use help with:
- fixing the intermittent errors that popup on the top 2 examples
- making the ISTRUE function to work with a range of cells, eg: A9:C9
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,122,912
Messages
5,598,828
Members
414,260
Latest member
joishe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top