VBA Function in VBA Function?

[katk]

Hi,

I've been trying to teach myself VBA coding so that I can use it at work. This morning, I made a UDF ("Brand") that worked. However, now I want to make another UDF that incorporates the first one, and it's not working and I don't know enough to know why. I've tried changing it in a hundred little ways to debug it, but it keeps coming up with different problems. The point of it is to only list the Brand (which is the result of the Brand function) when it differs from the Brand for the cell above. Can anyone help me out? Thank you so much.

Function ApplyBrand(num)
If Brand(num) = Brand(Range("num").Offset(-1, 0)) Then
x = 1
End If
Select Case x
Case Is = 1
    ApplyBrand(num) = " "
Case Else
    ApplyBrand(num) = Brand(num)
End Select
End Function

 

[wigi]

Can you give an example of how you use the formula in a worksheet?

 

[VoG]

It depends on the argument that you are supplying to the function. Maqybe

Function ApplyBrand(num As String)
If Brand(Range("num")) = Brand(Range("num").Offset(-1, 0)) Then
    x = 1
End If
Select Case x
Case Is = 1
    ApplyBrand(num) = " "
Case Else
    ApplyBrand(num) = Brand(num)
End Select
End Function

 

[wigi]

Or another guess:

Function ApplyBrand(num As String) As String
    If Brand(Range(num)) <> Brand(Range(num).Offset(-1)) Then ApplyBrand(num) = Brand(Range(num))
End Function

 

[katk]

A1 will have an item number. The "Brand" function looks up the brand for that item from another sheet. The ApplyBrand function would be in B1, and then the item name in C1. Column A gets hidden when we print (this is a price book), so the point of ApplyBrand is to list the Brands of each item but only once is items of the same brand are grouped together, to keep it cleaner. Eg:

(121) LEELANAU Winter White
(122) Great Lakes Red
(123) Witches' Brew
(141) DEANGELIS Red
(151) TAYLOR Chardonnay

 

[katk]

Oops, sorry. This should look a bit clearer:
(121) | LEELANAU | Winter White
(122) | --- | Great Lakes Red
(123) | --- | Witches' Brew
(141) | DEANGELIS | Red
(151) | TAYLOR | Chardonnay

 

[wigi]

- you probably do not need a VBA UDF function, as I *think* ordinary Excel functions will suffice to look up the brand.

- to "hide" entries in the sheet, conditional formatting to change the font color will be more interesting than again a UDF function.

 

[katk]

Thanks wigi and VoG, I think I will just use conditional formatting this time. As an additional question, however, did you learn VBA coding through other computer knowledge or through a specific resource? I'd love any suggestions if you have them... but thanks for the help you've already given!