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!

 

[offthelip]

Something like this should do it:

Function newnu(Ref As Range, Var1, Var2)

Refv = Ref.Value
Up1 = Ref.Offset(-1, 0).Value
If Up1 = Refv Then
newnu = Var1
Else
newnu = Var2


End If

 

[Eric W]

Welcome to the forum.

First of all, that formula is not written well. It's not usually required to use INDIRECT and ADDRESS because Excel automatically updates the references when you copy/paste or drag a formula. Your formula can be rewritten as:

=IF(A2=A1,B1,IF(B1=1,2,1))

Second, VBA is generally far different from spreadsheet formulas. You can use EVALUATE to evaluate a string as a formula, but there's usually a better way using native VBA. Your code is giving errors since it is not standard VBA language.

So try the formula first and see how that works for you. We could write something in VBA, but the native formula I provided will be much quicker and should be easy enough for you to see.

 

[Eric W]

Incidentally, you can get the group banding effect using just Conditional formatting. Assuming "Reference" is in A1, select A2:A1000 (whatever your bottom row is), click Conditional Formatting > New Rule > Use a formula and enter:

=AND(A2<>"",MOD(SUMPRODUCT(--(A$1:A1<>A$2:A2)),2))

click Format and choose a fill color.

 

[dmikhailov4]

Eric W,

I appreciate the reply, but the use of Indirect was intentional because if you delete a row, for example, you get a reference error, so this is a fool-proof fail-safe for that. Your formula is what I used in the past, until I got that problem.

I will play with the Evaluate recommendation tho, but I think you're right about native VBA which is what I'm seeking.

Thanks Again,
Daniel

 

[dmikhailov4]

Incidentally, you can get the group banding effect using just Conditional formatting. Assuming "Reference" is in A1, select A2:A1000 (whatever your bottom row is), click Conditional Formatting > New Rule > Use a formula and enter:

=AND(A2<>"",MOD(SUMPRODUCT(--(A$1:A1<>A$2:A2)),2))

click Format and choose a fill color.

Eric W,

Thanks also for this, but it's not working as intended. Basically myconditional format reads as "=$B1=2" then fill light blue. You'reright that my original formula could be put directly into the conditionalformatting formula, but conditional formatting is VERY finicky, if you dosomething like insert rows or even something as simple as copying one cell intoanother, so merging the two would be much harder to maintain.

I did try yourformula though, and it didn’t seem to work I’m afraid – not sure why, but Iwonder if the formula you have above is an array formula (never used arrays inconditional formatting).

Thanks againanyhow!
Daniel

 

[dmikhailov4]

Something like this should do it:

Function newnu(Ref As Range, Var1, Var2)

Refv = Ref.Value
Up1 = Ref.Offset(-1, 0).Value
If Up1 = Refv Then
newnu = Var1
Else
newnu = Var2


End If

OffTheLip,



Thanks so muchfor taking a stab at this. The OFFSET is a great idea. This is definitely inthe right direction, but where you have “Up1” Defined to return the value of the cell above the the Reference (in the example it would be A1), I instead what it to be Up1 of the cell containing the actualformula (i.e., B1 in the example).



Likewise, thereis another If statement nested here such that if the Reference and the oneabove are not equal, and if the cell above the formula cell is Var1,then it should be Var2; if Var2, it should be Var1.



So to modifyyour code it would read:

Function newnu(RefAs Range, Var1, Var2)



Refv = Ref.Value

Up1 =Ref.Offset(-1, 0).Value



If Up1 = RefvThen

newnu = <s>Up1</s>Up2

‘ newnu should equal the cell directly above where the formula is (incolumn B), but Up1 would point to the cell above the Reference (in column A). Howdo I point to the cell above the formula?



ElseIf Up2= Val1 Then

newnu = Val2

Else

newnu = Val1



End If


End Function

 

[offthelip]

I think you are going to need to use the Application.caller method see:
https://answers.microsoft.com/en-us...orksheet/b861723e-c7aa-4944-b15a-5cda0d8d3da8

Note I have never used this, i will let you work it out.

 

[Eric W]

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.

 

[Peter_SSs]

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

BTW, what are you doing about row insertions?

Excel Workbook
	A	B
1	Reference
2	Dog	1
3	Dog	1
4	Cat	0
5	Cat	0
6	Cat	0
7	Horse	1
8	Sheep	0
9	Sheep	0
Numbers for banding