How do I find one or more zeros in an Array ?

[nco]

Hi super Excel users

I have one more questions for you today.
Hope you can help me out on this one.

The essence of my problem is:
I have an Array which contain 200 to 300 numbers, (in one dimension only)

Then this Array can contain one or more zeros and I want to get the index.numbers of those zeros.
If the Array do not contain any exact zeros it might contain one or more numbers that are pretty close to zero and have zero slope (saddle points). If the Array doesn't contain any exact zeros I want to get the index numbers of those points being close to zero and at the same time have zero slope.

Is it possible to do this in pure VBA in an easy way without writing all the numbers in the Array to a range in Excel ?


Hope one of you nerds out there can help me out on this one.

Regards
NCO
Denmark

All for today

 

[Scott Huish]

What is your definition of pretty close to zero and what is zero slope?

 

[nco]

Finding zeros in an Array

Hi Hotpepper

I have been thinking more about the problem.
And has come to the conclusion that I should forget about the values close to zero, and if there aren't any zero solutions then pressent a pop up window to the user explaning that he or she has to enter new numbers in order to get exact solutions.

So the problem is now reduced to finding 1 or 2 zeros in an Excel VBA Array.
Preferrably purly in VBA - coding and not writing the complete Array to a sheet.


Hope you have some inputs.
Regards NCO

 

[Scott Huish]

How is the array being filled with values?

 

[nco]

Zeros in an Array.....

Hi Hotpepper

The code that assigns values to my Array's is listed below.
What I actually are interested in is the index number of Vave_abs_diff(i) at which Vave_abs_diff is zero.
This index number shall I then use to send k(i) back to the sheet.

Hope you or anyone else has some useful ideas.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo Theend
'
Dim input_cell_1 As Single, input_cell_2 As Single
Dim help_var1 As Double, help_var2 As Double
Dim Cal_cell As Double
'
Const k_start_val As Single = 1
Const k_stop_val As Single = 4
Const k_step_val As Single = 0.01
'
Dim k(1 To ((k_stop_val - k_start_val) / k_step_val)) As Single
Dim Vave_abs_diff(1 To ((k_stop_val - k_start_val) / k_step_val)) As Double
Dim i As Integer
'
'
If OptionButton3 = True And Intersect(Target, Range("Input_Cells")) Then
MsgBox "I'm running_option_3"
'
'* Brug formlen for udregning af shape parameteren med parameterne
'A & Vave som kendte. Placer den udregnede størrelse i relativ ref. A1.
'
input_cell_1 = Range("input_cells").Range("b1")
input_cell_2 = Range("input_cells").Range("c1")
'
For i = 1 To ((k_stop_val - k_start_val) / k_step_val)
If i = 1 Then
k(i) = k_start_val
Else
k(i) = k(i - 1) + k_step_val
End If
'
help_var1 = Application.WorksheetFunction.GammaLn(1 + (1 / k(i)))
Cal_cell = input_cell_1 * Exp(help_var1)
Vave_abs_diff(i) = Abs(Cal_cell - input_cell_2)
'
ThisWorkbook.Worksheets("Power_curves").Range("e3:e500"). _
Rows(i).Value = Vave_abs_diff(i)
Next
'
End If
'
Theend:
Application.EnableEvents = True
End Sub


Regards
NCO

 

[Scott Huish]

Not suire if I quite follow what you're doing but you could run it through a loop, for example:

For x = 1 To UBound(Vave_abs_diff)
If Vave_abs_diff(x) = 0 Then
'Your Code Here
End If
Next x

or you could write another array with the values, something along the lines of:

For x = 1 To UBound(Vave_abs_diff)
If Vave_abs_diff(x) = 0 Then kcount(x) = x
Next x