VB: UDF for combine ISNUMBER and if >0

[Davexx]

hi, i am not sure of the ins or outs for udfs, if they slow things down, add exponential file size, or what to call.. unstable? for name error popping up unless manually hit "enter" on cell and error goes away. i can post examples on some of that, but the question is

is there a way to make a udf for combining: =IF(AND(ISNUMBER(A1),A1>0),1,0)
this is the second most repeat item using. guesse idea to "call as" idea might be: =IF(ISTRU(A1),1,0)
cannot think of a better name.. thanks.
don't see what trackbacks or tags are.

might be first post.. if i dont see my posts on a my profile page... will try to keep a link for this page.



i could make it a separate post, but is much related. the first most used - too long a formula is for tesing if names / characters exist in a cell / range..
and to make a UDF for something similar, am using eg:

SUMPRODUCT(--ISNUMBER(FIND(Lx,CV9:CW9)))>0


Lx is a defined name having variables eg: (use is case-sensitive)

={0;"S";"T";"U";"M"}

wouldn't believe the number of times used to just test a few characters.
to combine sumproduct, --isnumber, find,
eg call might be: =SUMF(Lx,CV9:CW9)>0

 

[Aaron Blood]

UDF's have a tendency to run slower than native cell formulas. They have to be compiled at runtime and each instance of a UDF in a cell makes a separate call to run the little macro. If not widely used throughout the spreadsheet, the impact would be unnoticeable. I don't have any problems with "stability" on a cell-by-cell basis... where I think people run into trouble with them is more likely in how they are implemented in the workbook overall design, which can make the entire workbook as a whole feel "unstable". ...but that's more of an overall layout/design issue.

The UDF code might look like:

Function IsTru(ByRef x As Range) As Boolean
    IsTru = False
    If x.Value > 0 Then IsTru = True
    If Not IsNumeric(x.Value) Then IsTru = False
End Function

As for your second question... you could probably look at the above syntax and derive the solution.
I would also maybe suggest a simpler format cell formula and maybe avoid the UDF:
=COUNTA(C14:C18)=COUNT(C14:C18)

...understanding that if CountA and Count are equal then the cell must contain all values. I was not exactly sure what you were trying to return in the second formula.

 

[Davexx]

is there a way, / am i supposed to mark this answered?
note to site. prefer to have a voluntary setting to not post item for 1 hour or time desired, so can make edits > 1 hour away. i try to do much research as can before submitting (initial post anyways), thanks.

----------
hi.. thanks, that seems to work well. will try to check stability with some medium use of it, eg: maybe 10 columns used once to set up a cell.
i guesse the separate call you refer to runs how often? same as a volatile action using a: =HYPERLINK ?
just curious on how microsoft makes that a NOT a separate call? / volatile?

i need to upgrade to a newer version of excel / windows (from xp - excel 2002). problem is suffering from severe file bloat. i saw some references on that. if you have a couple word idea on that, else can make separate post after i make a large research on it. guessing might need to find vb to fix. only test i did was to delete everything in a file (up to a point), and still had 5 or 6MB size file. after a (uspecific now) rem or delete of page, the 5MB went away.

thanks much.

 

[Davexx]

hi, that seems to work good for a single cell. i do not know how to look up for a range of cells. cannot guesse what terms might be used. (am novice at vb, and this looks like a unique area).
using:

Function IsTrue(ByRef x As RANGE) As Boolean   'call as:  IF(ISTRUE(A1),1,0)  cannot get to work:  IF(ISTRUE(A1:B1),1,0)
    IsTrue = False    'use for:  if cell isnumber & is greater than zero
    If x.Value > 0 Then IsTrue = True
    If Not IsNumeric(x.Value) Then IsTrue = False
End Function

what would expect, is that all cells would pass the test, not just any 1. thanks.
(note: i can tell i might not be able to make the sumproduct version myself.. not that good at vb, will make a separate post for that if i need it, later)


and to say: for now am using this in about 4 places x 1300 records, so some 5200 iterations. if any performance issues matter.