Running vba on 64 bit and 32 bit

[Perry72]

Hi everyone,

I'm new to vba so please bear with me ?

I'm using office 2016, 64 bit on win10 and have used code from online to create a function in excel to count coloured cells. This works fine using 2016, 64bit win10 and 2010, 64 bit win7 however it does not work on 2010 32 bit win7.

Any advise ?

 

[Perry72]

For reference below is the code in question

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

lCol = rColor.Interior.ColorIndex

If SUM = True Then

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.SUM(rCell, vResult)

End If

Next rCell

Else

For Each rCell In rRange

If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If

Next rCell

End If

ColorFunction = vResult

End Function

 

[Fluff]

In what way doesn't it work?
It works fine for me on 2013 32bit

 

[Perry72]

Hi Fluff,

Strangely enough when I got back to work I tried to replicate the problem and it seems to have been resolved. Thanks for responding and confirming it work in 2013 32bit

Now for the next question that I hope you or the forum to help with

Can you could be modified to only count visible cells. The theory is that I would be able to filter the table and then count the colours based on the filter.

 

[Fluff]

You could try

For Each rCell In rRange.SpecialCells(xlVisible)

 

[RoryA]

Specialcells doesn't work properly in a UDF, so you'd need to check rCell.Entirerow.Hidden in the loop instead.

 

[Fluff]

Never knew that,

 

[Perry72]

Would I be cheeky in asking if you could modify the code I posted to ignore hidden cells

Thanks

 

[RoryA]

Yes.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

lCol = rColor.Interior.ColorIndex

If SUM = True Then

For Each rCell In rRange
If not rcell.entirerow.hidden then
If rCell.Interior.ColorIndex = lCol Then

vResult = WorksheetFunction.SUM(rCell, vResult)

End If
end if
Next rCell

Else

For Each rCell In rRange
If not rcell.entirerow.hidden then
If rCell.Interior.ColorIndex = lCol Then

vResult = 1 + vResult

End If
End if
Next rCell

End If

ColorFunction = vResult

End Function

 

[Perry72]

Thanks Rory, the code worked an absolute treat

Regards,
Perry