1

I am attempting to check if any value in a dynamic array of size "i" by one is greater than zero where "i" is a user input. If false, the value of the first element in the array would show in the next column. If true, the values of the array update by essentially moving down one element. I believe I have found answers for this question in java here, but haven't had any luck finding it in vba. I believe this could be done with a for loop as done here, but would like something more efficient if it exists. Since I do not know any syntax which may work the code hangs at "If any x > 0 Then"

Dim i, z, u As Integer
Dim fir As Integer
Dim las As Integer
Dim n As Long
Dim x As Variant

'SET VALUE OF ROWS FOR ARRAY
i = Worksheets("INPUTS").Range("C6").Value

Set s = Worksheets("DATA")

'FIND LAST VALUE IN DATA
n = s.Cells(s.Rows.Count, "A").End(xlUp).Row

'BEGIN LOOP THROUGH DATA SET
For z = 1 To n

    'SET/RESET RANGE TO CHECK FOR CONSECUTIVE VALUES LESS THAN ZERO
    fir = z
    las = i + z - 1
    x = (s.Range("C" & fir, "C" & las).Value)
    u = s.Range("C" & UBound(x)).Value

    If any x > 0 Then
    Else: s.Cells(4, z) = x(1, 1).Value
    End If

Next z

I'm a beginner and new to the forum so feel free to tear me a new one on anything against best practices relating to the code or the question, thank you.

vbderr
  • 13
  • 1
  • 4
  • You should watch this series on YouTube:[Excel VBA Tutorial](https://www.youtube.com/watch?v=KHO5NIcZAc4&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5) . Episodes 5 and 25 are relevant. –  Aug 23 '17 at 04:57
  • Would you mind sharing the screenshots of what you have and what should be your expected output to visualize your goal? – Subodh Tiwari sktneer Aug 23 '17 at 05:44
  • 1
    `Dim i, z, u As Integer` only declares `u` as integer. The others will be declared as variants. Also, there's almost never a need for the integer type. Use `long` instead. See discussions in SO and on the 'net for explanations. – Ron Rosenfeld Aug 23 '17 at 11:02
  • dont mistake a range for an array. The later is faster, but needs more coding. – Patrick Lepelletier Aug 24 '17 at 00:21
  • Thank you for the input. @sktneer the screen shots don't explain the goal well, but the gist is just to identify a number of consecutive negative values in a column of data with the number of consecutive negative values pulled in as an input from the user. Once that criteria is met, the first value in the data set would be displayed in the next column. – vbderr Aug 24 '17 at 00:30

2 Answers2

0

In order to speed up the processing you may consider converting Excel Range (for example A1:A100) into array Arr and then iterating through array:

Dim Arr() As Variant
Arr = Range("A1:A100")

Also, you may consider setting property Application.ScreenUpdating = False prior to starting the iterations (in case you change the cells' values during iteration as per your example) and resetting it to True afterwards.

Hope this may help.

Alexander Bell
  • 7,842
  • 3
  • 26
  • 42
  • 1
    you have to also do `Arr = Application.Transpose(Arr)` to convert from 2D array into a 1D array – jsotola Aug 23 '17 at 04:56
  • 1
    @jsotola I've never done any speed test, but consider: would it be faster to just iterate through the first dimension of the 2D array? Or to do the transpose and then iterate through the 1D array? – Ron Rosenfeld Aug 23 '17 at 11:06
  • i do not know if it is faster or slower. just one less thing to cause issue eg _is it arr(1,x) or arr(x,1)_ – jsotola Aug 23 '17 at 17:19
  • @RonRosenfeld The answer will depend on the size of array: apparently, Transpose() function overhead increases the overall time complexity, but it could be justified in case of processing a large array. As a general rule, just iterating through the first dimension probably will suffice the case. Best regards, – Alexander Bell Aug 23 '17 at 17:19
  • @jsotola Now you've got me curious. I'll have to test it. – Ron Rosenfeld Aug 23 '17 at 17:20
  • @jsotola Sure, that would be interesting algo efficiency comparison test. Best regards, – Alexander Bell Aug 23 '17 at 17:22
  • 1
    @jsotola OK there are two problems with `Worksheetfunction.Transpose`. With an array of 32,000 rows, it took 3 times as long to sum the values as using the original 2D array. But also, it was somewhat erratic in dealing with large arrays. I initially tried an array of 100,000 rows, but transpose --> ubound of 34464. Some testing reveals that Transpose will only return a maximum of `num_rows MOD 65536`. – Ron Rosenfeld Aug 23 '17 at 17:47
  • @RonRosenfeld Interesting findings, thanks for sharing. In the light of this finding, the best practice will be looping through the first dimension, as stated above. – Alexander Bell Aug 23 '17 at 18:00
  • That was my "gut" impression, but hard data is always better. – Ron Rosenfeld Aug 23 '17 at 18:20
  • @RonRosenfeld, i just posted another answer using the worksheet function. your thoughts on execution speed would be welcome. – jsotola Aug 23 '17 at 22:11
  • @jsotola There's no real way to know without testing. But on a range of 100 cells, I would think the differences to be insignificant. – Ron Rosenfeld Aug 23 '17 at 23:43
  • @RonRosenfeld from your earlier comments, i gathered that you ran some kind of an execution time test on the array, and i thought that you could also run a test on this also, for maybe `Application.WorksheetFunction.Max(Range("a1:a100000"))`, if you have the time – jsotola Aug 24 '17 at 00:05
  • @jsotola the `MAX` function is significantly (12-15 x) faster than looping through an array to find the highest value. – Ron Rosenfeld Aug 24 '17 at 00:15
  • @RonRosenfeld, thanks for checking that. i guess that, if there is a worksheet function available, use it because it has been optimized all the way to the bone. – jsotola Aug 24 '17 at 00:29
  • @jsotola That has not always been the case, in my experience. Best to test optimal VBA code against the equivalent worksheet function if speed is the goal. – Ron Rosenfeld Aug 24 '17 at 00:52
  • @jsotola To expand, my "gut feeling" would be that worksheet functions like `SUM`, `MAX` and the like, which can operate on a large range of cells in a single operation, would be more efficient than equivalent VBA routines, especially on larger ranges. But with the various `LOOKUP` functions, `MATCH` and maybe some others, especially when working with unsorted data, optimized VBA routines might be better. Again, this should be tested for the scenarios involved. And anything that involves repeated access to the worksheet will almost always be slower than using VBA arrays. – Ron Rosenfeld Aug 24 '17 at 12:12
0

use worksheet function

If Application.WorksheetFunction.Max(Range("a1:a100")) > 0 Then
    MsgBox "value higher than zero"
end if
jsotola
  • 2,238
  • 1
  • 10
  • 22
  • Thanks, this worked great and eliminated the need for any element by element check of the array. – vbderr Aug 24 '17 at 00:27
  • you can just do `Application.WorksheetFunction.Max(columns("a"))` and not worry about finding the last populated row. it still works if there is text in the column – jsotola Aug 24 '17 at 00:34