Pass Excel Range in VBA Function, Process as Array, and Return Result

Question

I have an Excel worksheet with some strings in a column. Sometimes all of the entries are the same, and sometimes not:

enter image description here

I wrote a function to pass the range as a parameter:

=Dent_WG(A1:A6)

The VBA function should determine which case is true (all entries = "Al", or at least one entry = "Ag"), then return 0 or 12 respectively:

Function DentWG(WG_Mat As Range) As Single

    Dim dat As Variant, rw As Variant, temp As Single
    dat = WG_Mat
    temp = 0
    
    For rw = LBound(dat, 1) To UBound(dat, 1)
        If dat(rw, 1) = "Ag" Then
            temp = 12
        End If
    Next

    If temp = 12 Then
        DentWG = 12
    Else
        DentWG = 0
    End If

End Function

However, the function always returns 0, even for the 2nd case where "Ag" occurs in the range. I'm sure I'm failing to correctly convert the range into an array or correctly apply the intended logic to that array.

Siddharth Rout · Accepted Answer · 2014-02-10 05:36:58Z

2

From your question...

The VBA function should determine which case is true (all entries = "Al", or at least one entry = "Ag"), then return 0 or 12 respectively:

This is what you need.

Function DentWG(WG_Mat As Range) As Long 
    Dim ClCount As Long

    ClCount = WG_Mat.Cells.Count

    If Application.WorksheetFunction.CountIf(WG_Mat, "Al") = ClCount Then
        DentWG = 0
    ElseIf Application.WorksheetFunction.CountIf(WG_Mat, "Ag") > 0 Then
        DentWG = 12
    End If
End Function

The same can be achieved using a formula

=IF(COUNTIF(A1:A6,"Al")=(ROWS(A1:A6)*COLUMNS(A1:A6)),0,IF(COUNTIF(A1:A6,"Ag") > 0‌,12,""))

In case it will always be 1 Column then you don't need *COLUMNS(A1:A6). This will do.

=IF(COUNTIF(A1:A6,"Al")=ROWS(A1:A6),0,IF(COUNTIF(A1:A6,"Ag") > 0,12,""))

ScreenShot

enter image description here

edited Feb 10, 2014 at 5:36

answered Feb 9, 2014 at 19:37

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

3 Comments

jmaz Over a year ago

@Siddharth Rout: +1 and check. Thanks for these two solutions. They both work fine for me. My own solution, which I have corrected above, also works. I see your solution invokes Application.WorksheetFunction. In my simple application, resources are not an issue, but generally speaking: Is it more resource intensive to invoke Application.WorksheetFunction within VBA code than to strictly stick with VBA methods?

jmaz Over a year ago

@assylias: On the contrary, thanks for correcting my loop counter.

assylias Over a year ago

It's done - I'm only a casual vba-er and you can tell the difference with pros! ;-)

maybeWeCouldStealAVan · Accepted Answer · 2014-02-09 20:07:26Z

2

You don't really need a UDF for this. You could just say:

=IF(COUNTIF(A1:A6,"Ag")>=1,12,0)

edited Feb 9, 2014 at 20:07

answered Feb 9, 2014 at 19:18

maybeWeCouldStealAVan

15.6k2 gold badges25 silver badges32 bronze badges

6 Comments

Siddharth Rout Over a year ago

+ 1 For a non VBA solution :) However you missed one part which case is true (all entries = "Al", or at least one entry = "Ag")

Siddharth Rout Over a year ago

You still forgot about "Al" :)

maybeWeCouldStealAVan Over a year ago

@SiddharthRout Yes, but from his code I assumed that the OP assumed that the inputs were reliably "Ag" or "Al". Admittedly, that's a lot of assuming :-)

Siddharth Rout Over a year ago

See the screenshots attached. and this part of the question

The VBA function should determine which case is true (all entries = "Al", or at least one entry = "Ag"), then return 0 or 12 respectively:

Siddharth Rout Over a year ago

I assumed that the OP assumed that the inputs were reliably "Ag" or "Al". Admittedly, that's a lot of assuming :-) BTW read the second sentence of the Question... Sometimes all of the entries are the same, and sometimes not :)

|

Alex P · Accepted Answer · 2014-02-10 17:53:11Z

2

This works for me:

Function DentWG(WG_Mat As Range) As Single
    Dim result As Single, cl as Range
    result = 0

    For Each cl In WG_Mat
        If cl = "Ag" Then
            DentWG = 12
            Exit Function
        End If
    Next cl

    DentWG = result
End Function

edited Feb 10, 2014 at 17:53

answered Feb 9, 2014 at 19:03

Alex P

12.5k7 gold badges55 silver badges74 bronze badges

3 Comments

Siddharth Rout Over a year ago

This also doesn't answer the question.

jmaz Over a year ago

@Alex P: does not work for me, perhaps because cl is not declared?

CarloC Over a year ago

Exiting a function from within a loop...Really?

Collectives™ on Stack Overflow

Pass Excel Range in VBA Function, Process as Array, and Return Result

3 Answers 3

3 Comments

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related