excel custom function for a loop

Question

I'm a newbie to excel VB so I hope some one can help me with this: I'm trying to calculate the row number of the first cell which doesn't have the value zero in a column(eg:if it's 0 0 0 1 should give me 4) but have a hard time in making it display on the worksheet, Here is my code-

Function Module1(y As Integer)
Dim x As Integer
x = 1
Dim a As Integer
a = 0
Do While x < 100 Or a <> 0
    If Cells(x, y).Value = 1 Then
    a = x

x = x + 1
Loop
Cells(y, 101).Value = a
End Function

y is the column number I want to get this function and there are 100 columns.

anefeletos · Accepted Answer · 2014-10-12 21:50:55Z

1

You declare a function 'Module1'. Not a good name for a function because 'Module1' used to be the name of the code container 'Module1'.

So:

press Alt+F11, right click on your Project, add a module (autonumbering gives your new module the name Module1, Module2 etc), type this code:

Public Function GetNonZeroRank(r As Range)
x = 1
Do While x <= r.Rows.Count And a = 0
    If r.Cells(x, 1).Value <> 0 Then
    a = x
End If
x = x + 1
Loop
GetNonZeroRank = a
End Function

You can use this function from cell A101: type in the formula:

=GetNonZeroRank(A1:A100)

Thats how to use (Public) Functions

Important: you can't change cell values from inside a function called from a sheet:

Cells(y, 101).Value = a

you can do this inside a Sub method

edited Oct 12, 2014 at 21:50

answered Oct 12, 2014 at 21:44

anefeletos

7027 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ioannis · Accepted Answer · 2014-10-12 23:52:57Z

0

A few points, in addition to those made by the other responses:

By your code it seems that you are looping over 100 rows and the result is displayed in row x (which is the first non zero row of column y) and column 101. Is this what you really want?
Why a function and not a subroutine? Unless you want to use it from the spreadsheet (in which case you can change only the content of the cell from which you call it, generally speaking), a function is not useful here. It would be useful if you would assign the return value of a function to another variable.
your code will crash if you go more than row 32,767, which is the biggest number an Integer can hold. I can see that you need your code to run until row 100, but I would suggest using Long, which is more robust and actually faster to evaluate.
Your code exits the loop under the condition a<>0 when a cell value equals one. In your description you state that you want to get the row number of the first non-zero cell, which, in general, it does not have to be the first non-zero cell that equals to 1. Which of the two do you actually want?

Actually, there is a trick that avoids the loop altogether (see below).

 Sub test1()
    test 1
End Sub

' Optional passes a default value to the argument. 
' If no value is given here,  then y = 1
Sub test(Optional y As Long = 1) 
  Dim x As Long
  With Sheet1
    x = .Evaluate("MATCH(1,--(" & .Range(.Cells(1, y), .Cells(100, y)).Address & "<>0),0)")
      .Cells(101, y) = x
  End With
End Sub

The above uses EVALUATE to parse an excel formula.

The idea is that

The array part is (example for column A) A1:A100<>0, which is a binary array that looks like {FALSE, FALSE, ..., TRUE, ...}
--{FALSE, FALSE, ..., TRUE, ...} Evaluates to {0, 0, ..., 1, ...}
MATCH(1,{0,0, ...,1, ...},0) returns the position of the first 1 that is found
I would also wrap this expression in an IFERROR statement to control what happens if all values are 0.

We can actually test the excel version of this formula in a cell. It has to be array entered (confirmed with Control + Shift + Enter), which happens automatically when entered from VBA.

I hope this helps!

edited Oct 12, 2014 at 23:52

answered Oct 12, 2014 at 23:47

Ioannis

5,4682 gold badges21 silver badges32 bronze badges

2 Comments

Automate This Over a year ago

I wouldn't discourage a function for this. A function returns a value and a sub does not. It's really not specific to in-cell formulas as it can be very helpful to utilize functions in other subs to modularize your design into reusable code and preventing duplicate less maintainable code.

Ioannis Over a year ago

@PortlandRunner I agree with the modularization argument and that the use of functions is not specific to in-cell formulas. My sense is that the modularization argument is not relevant in this particular context (using a sub does not make the design less modular), and if the OP does not intend to call this procedure from a cell, I think a sub is a more elegant choice. Also, the OP does not seem to return anything from the function on his original code, so I suspected that he might not be aware of subs in the first place.

Collectives™ on Stack Overflow

excel custom function for a loop

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related