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:
i have been forced to use vb for a manual type: hit enter on cell:
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)
'GETS NAME ERROR, HIT ENTER ON CELL TO FIX: (note: need help with test range of cells)
'========== SAMPLE THAT IS STABLE: QLINK: used as alternative to hyperlink using: SCROLLROW, no more offsets.
'code in a module:
and the 2nd part within the sheet:
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
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: