Create VBA Function using existing Excel Functions

[dmikhailov4]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reference

[/TD]
[TD]Result of Formula

[/TD]
[/TR]
[TR]
[TD]Dog
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Dog
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Cat
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Cat
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Cat
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Horse
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Sheep
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Sheep
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]

Hi,


I’m hoping someone can help me with writing a VBA code to create a function that converts a formula I use to an easier function, using existing Excel Functions (namely, INDIRECT, ADDRESS, ROW, COLUMN):

Regular Formula
=IF(A2=INDIRECT(ADDRESS(ROW(A2)-1,COLUMN(A2))),INDIRECT(ADDRESS(ROW()-1,COLUMN())),IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))=1,2,1))

…into this
=GROUPBANDING(Ref,Val1,Val2)…where in the above formula Ref=A2, Val1=1, Val2=2.

Description of whatthe formula is doing:
As per the table above, IF the Ref cell equals the row above it, the Result equals the Result above that (in the 2^nd column). Ifnot, it equals an alternative value. In this case, the numbers alternatebetween 1 and 2 (later, I use this to conditionally format all the 2s to befilled with another color – it’s like colored banding, but instead ofalternating between every row, it alternates between every group of referencecells - I can't get colors to work in the example table above, though).


My current VBA Code:
-------------------------------------------------------

Function GROUPBANDING(Ref, Val1, Val2)

If Range(Ref) =INDIRECT(ADDRESS(ROW(Ref) - 1, COLUMN(Ref))) Then

INDIRECT(ADDRESS(ROW() - 1, COLUMN()))
​

ElseIf INDIRECT(ADDRESS(ROW() - 1, COLUMN())) = Val1 Then
​

Val2
​

Else
​

Val1
​

End If

End Function
-------------------------------------------------------

Error message I get:
“Compile Error: Sub or Function not defined” and points to the first instance of “ROW”

I’ve not needed to make Functions in the past so I’m not as adept at these, so any help or guidance would be appreciated! Thanks!

 

[dmikhailov4]

Here is another option that should be fine with row deletions.
Leave B1 empty

BTW, what are you doing about row insertions?

Numbers for banding

	A	B
Reference
Dog
Dog
Cat
Cat
Cat
Horse
Sheep
Sheep

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 77px;"><col style="width: 39px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]

[TD="align: right"]0[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]

[TD="align: right"]0[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(A2=INDEX(A:A,ROW()-1),INDEX(B:B,ROW()-1),1-INDEX(B:B,ROW()-1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Peter, thank you, but I am looking to make a VBA function so that instead of a full formula (either yours or mine) I can just put in the variables. The Indirect functions I have in my original formula (=IF(A2=INDIRECT(ADDRESS(ROW(A2)-1,COLUMN(A2))),INDIRECT(ADDRESS(ROW()-1,COLUMN())),IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))=1,2,1)) already solves the row deletion issue.

 

[dmikhailov4]

Well, if you are concerned with deleting or adding rows, try this formula:

=IF(A2=OFFSET(A2,-1,),OFFSET(B2,-1,),IF(OFFSET(B2,-1,)=1,2,1))

in B2. There are only references in the current row, so it should work fine. If you really want a UDF, then try:

Public Function GroupBanding(ByVal Cell1 As Range, ByVal Cell2 As Range) As Long

    If Cell1 = Cell1.Offset(-1) Then
        GroupBanding = Cell2.Offset(-1)
    Else
        GroupBanding = IIf(Cell2.Offset(-1) = 1, 2, 1)
    End If
        
End Function

where the formula in B2 would be:

=GroupBanding(A2,B2)

Finally, the CF formula I showed you does work for me, but like you said CF can be finicky, especially when you add/delete ranges. The formula I showed was not (technically) an array formula. However, as it happens CF can handle array formulas.

Hope this helps.

Eric,

Thanks for your continued help! The issue with this new code you provided is that you are leveraging column B which contains my original formula. I want the result of this new function to be in column B. I cannot use this new code you wrote unless I used column B, which basically defeats the purpose. I want the result of this new function to be in column B.

 

[dmikhailov4]

Eric,

Thanks for your continued help! The issue with this new code you provided is that you are leveraging column B which contains my original formula. I want the result of this new function to be in column B. I cannot use this new code you wrote unless I used column B, which basically defeats the purpose. I want the result of this new function to be in column B.

Re my above response, nevermind. Your code works like a charm! I thought you wanted to leverage what I already had in B2 and used column C for your formula.

Thank you!
Daniel

 

[dmikhailov4]

Well, if you are concerned with deleting or adding rows, try this formula:

=IF(A2=OFFSET(A2,-1,),OFFSET(B2,-1,),IF(OFFSET(B2,-1,)=1,2,1))

in B2. There are only references in the current row, so it should work fine. If you really want a UDF, then try:

Public Function GroupBanding(ByVal Cell1 As Range, ByVal Cell2 As Range) As Long

    If Cell1 = Cell1.Offset(-1) Then
        GroupBanding = Cell2.Offset(-1)
    Else
        GroupBanding = IIf(Cell2.Offset(-1) = 1, 2, 1)
    End If
        
End Function

where the formula in B2 would be:

=GroupBanding(A2,B2)

Finally, the CF formula I showed you does work for me, but like you said CF can be finicky, especially when you add/delete ranges. The formula I showed was not (technically) an array formula. However, as it happens CF can handle array formulas.

Hope this helps.

Eric,

Sorry for the multiple messages. Is there a way to write this code so that you don't have to provide B2 in the function arguments (that is, so that you don't have to write =GroupBanding(A2,B2) but instead only =GroupBanding(A2) )? In other words, is there a way to reference the current cell you are writing the formula in?


Also, do you know how to make the function show what variables are needed? For example, when you use a CountIf function it animate =COUNTIF(Range, Criteria)

Thank you!
Daniel

 

[Eric W]

I'm not sure why you want to remove the B2 reference. Yes, we can remove it IF we have another way to identify the current column. Is it always just 1 column to the right of the first cell? If so, we can use Cell1.Offset(-1,1) in place of Cell2.Offset(-1,). Or you can change the second parameter from B2 to the number of columns over. But the problem with both of those solutions is that it makes the function LESS flexible. If you were to add a column between A and B, the function would break. Whereas, if you explicitly use A2 and B2 in the parameter list, the function would automatically adapt.

As far as making the function show the parameters (which is called Intellisense), as far as I know there's no native way to do this. If that's something you really want to do, there's an Add-in to make it work:

https://fastexcel.wordpress.com/201...dfs-part-15-adding-intellisense-to-your-udfs/

 

[dmikhailov4]

To your question: "I'm not sure why you want to remove the B2 reference. Yes, we can remove it IF we have another way to identify the current column. Is it always just 1 column to the right of the first cell?"

I'm glad you asked this actually. So in the main IF function, the TRUE value is supposed to calculate this: INDIRECT(ADDRESS(ROW()-1,COLUMN()))

Notice, there is not any cell reference in this formula at all, EXCEPT implicitly. If my formula is itself found in cell B2, ROW() returns a 2, & COLUMN() returns a 2 because if you leave the parentheses empty, Excel default the the cell where the formula is written. Therefore, ADDRESS(2-1,2) returns B1. INDIRECT(B1) gives me the value in B1,without ever having to reference B2.

Therefore, you can have this work in any column.

How that would work in VBA (i.e., how to reference the cell of where you're writing a formula), I have no idea.

Re the Intellisense, I think you're right about animating the arguments in real time. I think it can show in the "Fx" pop-up box though. This is not really urgent.

Thank you so much for your help! If there is not a quick answer for the first part, no worries! Don't go out of your way!