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]

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 replace the errors in r1, or detecting, removing or replccing errors myself before the udf is used, is there a way (what is the way?) to replace any (and all) errors in r1 with a 0, string or even blank, before passing that "new" r1 to the rest of the function - and if possible (though would be ok with that too if it works in short term) without using for loops and/or the use of lbound & ubound , and without changing the source range in the sheet?

I.e. the source range r1 would still be the same (with all original values in it unchanged and untouched), but what is eventually passed to r1 has any and all errors in it dealt with (replaced with strings or zeros) so the udf can calculate properly as if there were no errors in the original r1 (like it does anyway now, when there are no errors in r1)?

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)

 

[Joe4]

Please post the entire code in your function.
It may be possible to handle the errors right in the code, but we need to see what it is actually doing.

 

[Spyros13]

Ok Joe. thank you. Just charging my computer from dead battery. Give Me 5 10 minutes.

 

[Spyros13]

Ok Joe. thank you. Just charging my computer from dead battery. Give Me 5 10 minutes.

Seriously I am. Don't like to walk into the kitchen. Im in the garden. That's where I wrote my forum message originally. But Had to charge my laptop soon after & walk to kitchen to plug it in . It's still there. But since no privacy or respect from there, don't want to do any work from there. Prefer here in the garden as it's safer. I will wait a bit more before I walj in & Get laptop again to send you the code. I live in a very difficult situation .

Can barely work without disturbance rude commets snarky statements most days. So on my day off when I want some peace I have to distract myself from the chaos & invasive situation . I could do it from my bedroom .. but it's too dark & dingy & uninspiring .. I'm the only one who works here so why should I be always rammed up in there on my day off? I'll post the code In a bit. At my wits end with my living aragemwnts .

 

[Spyros13]

Ok, it still works , its the 31th itteration of this udf because i kept trying differrent things to deal with the errors & failing thats all.


You can see ive commented them out. It started as 1 range first (column ) but wanted to do it for rows too. It works for each.

Function My_Func31(R1 As Range, DimensionA As Long) As Variant


On Error Resume Next

'replaceErrors (r1)


'For Each cell In r1

'Dim r1 As Variant



'rng = r1.SpecialCells(xlCellTypeFormulas, 16)
    'ans = MsgBox("There are " & rng.Count & " errors." & vbCrLf & "Do you want to replace them with 0?", vbYesNo)
    'If ans = vbYes Then
 '   rng.Value = 0
'https://www.mrexcel.com/board/threads/count-of-errors-using-vba.165375/
'r1 = Replace(rng, rng.SpecialCells(xlCellTypeFormulas, 16), "0")

'If IsError(cell) Then



'Dim Myarray0 As Variant
'Dim i As Integer

'Cells.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
'Range(r1).SpecialCells(xlCellTypeFormulas, xlErrors).Clear

'TestSub_


'r1 =
'(r1, 0)

'Myarray0 = r1

Dim Myarray As Variant
Dim Myarray2 As Variant
Dim arr1 As Variant

Dim Saa1, Saa2, Saa3 As Variant

arr1 = R1 'Chr(34) & r & Chr(34)

'For i = LBound(Myarray0) To UBound(Myarray0)

'For Each mycell In r1
 '   If IsError(Myarray0(i)) Then
  '      Myarray0(i) = 0
   ' Else
    '    Myarray0(i) = Myarray0(i)
 'End If
'Next i

'If d = 1 Then
''Dim cell As Range

''For Each cell In r1
''If IsError(cell) Then
''r1 = (r1.Address)
''Else:
''r1 = r1
''End If
''Next




Set R1 = R1

 
If DimensionA = 1 Then
'arr1 = Myarray0

Myarray = Application.Transpose(arr1)
Saa1 = Application.Evaluate("LOOKUP(" & "2,1/(" & R1.Address & "<>" & Chr(34) & Chr(34) & ")," & R1.Address & ")")

Debug.Print (Saa1)


Saa2 = "|" & Saa1 ' & "|"
Debug.Print (Saa2)


Saa3 = Join(Myarray, "|")
Debug.Print (Saa3)

End If

If DimensionA = 2 Then

'Application.Transpose(R1) = B1


Saa1 = Application.Evaluate("LOOKUP(" & "2,2/(" & R1.Address & "<>" & Chr(34) & Chr(34) & ")," & R1.Address & ")")

Debug.Print (Saa1)

Myarray2 = Application.Transpose(Application.Transpose(arr1))


Saa2 = "|" & Saa1 ' & "|"



Debug.Print (Saa2)

Saa3 = Join(Myarray2, "|")
Debug.Print (Saa3)

End If

'On Error GoTo 0

My_Func31 = Len(Left(Saa3, InStrRev(Saa3, Saa2))) - Len(Replace(Left(Saa3, InStrRev(Saa3, Saa2)), "|", "")) + 1


End Function

You can see its an alternative novel other way to calculate the last used cell in a range of column or rows. I made it. And I swear before it ignored errors and stil worked, but now if there is an error in any range it doesnt . was trying to get around that or deal with that so user doesnt have to manually check or remove them or find out at writing time.


Maybe convert r1 to string range!?!? idk. Want to avoid if possible loops or calling external private subs or anything that could complicarte it, because, imo , its beauty was in its blinding obvious simplicity/alterntive approach (though perhaps uneccissary. since we have other methods to do same)

 

[Spyros13]

Like i already used xlUp and Xltoleft/right in another similar function. And already used ubound lbound . I wanted to make a function to do the same without them. so it works as is. Only want to deal with errors and work regardless of them present or not in the range r1.

 

[Spyros13]

Though I really dont know what im doing anymore . Area/events havnt helped at all. Im going to have to look at this again with fresh eyes.

(p.s. note for me: Excel VBA assign a range to a string variable.)

 

[Joe4]

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:

(p.s. note for me: Excel VBA assign a range to a string variable.)

 

[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.