Running vba on 64 bit and 32 bit

Perry72

New Member
Joined
Jun 16, 2018
Messages
5
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 ?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
For reference below is the code in question

Code:
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
 
Last edited by a moderator:
Upvote 0
In what way doesn't it work?
It works fine for me on 2013 32bit
 
Upvote 0
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.
 
Upvote 0
You could try
Code:
For Each rCell In rRange.SpecialCells(xlVisible)
 
Upvote 0
Specialcells doesn't work properly in a UDF, so you'd need to check rCell.Entirerow.Hidden in the loop instead.
 
Upvote 0
Yes. ;)

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top