how to pass range in a vba function without errors in it & replace eerror with a string value ir 0 (without for loops)

[Spyros13]

is this possible?

For example I have Function:

My_Func(r1 As Range, DimensionA As Long) As Variant

if

r1

contains any error the whole function will return #value.

or if I have

Function My_Func(r1 As Range, DimensionA As Long) As Variant
On Error Resume Next

before the rest of the code, it returns

1

in the cell.

As long as

r1

dooesnt contain any errors, the UDF works and calculates intended results.

Without employing a subroutine first to replacre the erros in r1, or detecting, removing or replccing errors myself before the udf is used, i there (what is the way) to replace any and all errors in r1 with a 0, string or even "", before passing that new r1 to the rest of the function, - and if possible (though would be ok with that too if it works) without using for loops and/or the use of lbound ubound and without changing the source range in the sheet.

I.e. the range

r1

would be the same, but what is eventually passed to r1 has any errors in/from r1 source, dealt with (replaced with strings or zeros).

Is this possible. I was thinking the range.replace method might help , but im not sure & wouldnt know how to use employ that. (or i might I refer to some old udfs have, which I will look into, but appreciate any help assistance offered)

 

[Spyros13]

Yes . They are errors I put in the range just to test it.

Like a vlookup(1,some range of blank cells,2,0) and a #Ref error in the range too.

When I delete the errors , replace or remove them the UDF works again , and shows the correct result. But when I put them back it the udf returns #value .

I simply want my udf to return a correct calculated value either way.

I though that like in python I could force the range to be treated as string range or force errors to become strings. Like str(variable) . Might be a way to handle the errors.

 

[Spyros13]

It looks like your UDF is using that range in some formulas, which can be a bit problematic.
So what is exactly causing the errors that we are causing the issues?
Are they the result of formulas?
If that is the case, please post the formulas. We may be able to amend the formulas to get rid of those errors.

I am not sure what you mean by this:

I thought for a second to change the format of the range that was being passed to the array to a string , so vba reads all the errors as strings.. and is thus is able to do the calculation regardless.

 

[Joe4]

Like a vlookup(1,some range of blank cells,2,0) and a #Ref error in the range too.

When I delete the errors , replace or remove them the UDF works again , and shows the correct result. But when I put them back it the udf returns #value .

I simply want my udf to return a correct calculated value either way.

All things equal, it is usually better to handle the errors at their source instead of trying to work around them down the line.
So, for cases like VLOOKUPS that return errors because they do not find a match, that can easily be addressed with the IFERROR function, where you tell it what to return in place of an error, i.e.
change your VLOOKUP structure to this:

=IFERROR(VLOOKUP(...),"")

Then you won't have to worry about errors caused by the VLOOKUP function in your range.
The IFERROR can be used on many different functions to deal with errors right in the formula that is returning them.
See: IFERROR function

 

[Spyros13]

The errors are irrelivent. I want them in the range. Some are neccisary to exist as errors just as they are.

I deliberately put them in r1 range .

I want the udf to calculate it's intended value correctly , with errors or without errors in r1 range.

Hence the question.

So what I'm looking for is "how if/when range is fed to array or variable in vba , how, does one (in vba) replace or remove them in the code, for the array and it's intended calculation to work ?"

It already works when errors arnt in r1.

But I want the udf to work even if and when there are errors in r1.

 

[Joe4]

Rather than try to reverse engineer what your function is supposed to be doing, can you show me an example of your data and what the function is supposed to be doing to it?

 

[Spyros13]

for a row like this,

32 fwf

1 fwedf

32d

43 sd

f1f ff aaaa99999

my udf does - My_Func(I78:S78,2) = returns 7 . (correct. Last used/filled value in range i78:s78 is o78 - 7th column accross)


for a column like this:

7​

ELEPHANT

MONKEY

8​

GIRRAFFE & DOG

8​

test5

90​

0​

TIM

​

poo35dog

special values

ag

ha ha hello

this is what my udf does
=My_Func(B68:B83,1) = returns 15. correct. b82 is the last filled cell (15th cell) in the range.

What it does always is tell me the number of used cells in any range (or the last used cell - where it is - in the range.)

It always works with ant values (even formulae ! ) in those ranges, unless they are errors.


So was looking for a way to make a virtual range to copy paste the ranges into to calculate the last used cell there (that impossible Stack overflow says. But i don't think so) or replace any errors in the ranges before its fed to arr1, blanks, 0's or strings, or just remove any errors and do the calculation with them seperately within the udf (like in . =sumproduct(--iserror(range))) in the udf and add that number to the number I would get with udf (if i manage to learn/find out how to do it, namely to read/pass the range r1 in the udf without errors in it. So then it wouuld be 2 ranges. first range, r1A - r1 without error cells/values and second range, r1B - simply the number id get from an application evaluate sumproduct(--iserror(range) on r1)

Even with loops on r1 before hand in the udf like so,



For Each myCell In r1
If IsError(myCell) Then
r1.Value = 0
Else
r1.Value = r1.Value
End If
Next myCell

But that kind of loop works on selections and ranges in a sub. I DONT WANT TO CHANGE R1. I WANT TO CHANGE WHAT HOW THE UDF SEES R1. But I cant think now or see how id do this now in my fucntion. Kind of exuahsted & too bleary eyes to0 work out how id do it in a udf. Even through im sure ive done similar before.

 

[Spyros13]

Again, I do not want to physcialy ammend, correct or change the range (or whats in it). But i do now realise i need to change the way it is seen, or change the way it is passed to the udf , or change the way the udf read it (or it is read by the udf) ( so essently it automaticaly changes any errors in r1 to 0 or string or - iffy - an empty cell*)
.
*if the last used cell in range was an error (after a long gap of true blank cells) id still want to include it , so making empty is a no-no

 

[Spyros13]

& Yes I know , Xlookup could do this for me, effortlessly out of the box,

e.g. like

=ROW(XLOOKUP(LOOKUP(2,1/(B68:B83<>""),B68:B83),B68:B83,B68:B83,"x",0,-1))

= 82 (o82)

just like my udf's answer. Except xlookup works even when errors present in range r1, mine doesnt . So thats why I was trying to account for them or find out how..

 

[Spyros13]

xlookup is the fuction you always wanted & i love its reverse directional search ability (took MS 30 years , 30 years too late imo, to come out with that !! It should have been there from day 1, plus xlookups range returns which is another abvious ability which should have been able from time!!). But i still want my own udf, which works entirely differently to xlookup, to work with errors in the same way!

This is why I am asking the question ( and to increase & improve my own vba ability/functionality).

 

[Joe4]

The only thing I can think of is to iterate through the range, one cell at a time, which tends to not be very efficient.
But without wanting to fix the errors at the source, I personally cannot think of a better way to do what you want (not seeing there isn't a way, just that I cannot think of one myself).