Convert Excel Formula to VBA - Count Upper Case Characters in a String-CodePudding

=SUMPRODUCT(LEN($A$7) - LEN(SUBSTITUTE($A$7, CHAR(ROW(INDIRECT("65:90"))), "")))

This formula above counts the Upper Case Characters in a string. It works great. I can not seem to get it converted to VBA. I am substituting Application.WorksheetFunction where needed and I have made use of double-quotes for the indirect function.

(My Effort) MyCaps =Application.WorksheetFunction.SUMPRODUCT(LEN($A$7) - LEN(Application.WorksheetFunction.SUBSTITUTE($A$7, CHAR(Application.WorksheetFunction.ROW(INDIRECT(""65:90""))), "")))

CodePudding user response：

FYI, you don't need to use INDIRECT() at all with your formula in your worksheets. =SUMPRODUCT(LEN($A$7)-LEN(SUBSTITUTE($A$7,CHAR(ROW(65:90)),""))) works because of SUMPRODUCT(). Not sure why 'the internet' want to use INDIRECT() when you search for this. And, if you must, make the range of rows absolute $65:$90 and ready for dragging.

Translate this to VBA would be along the lines of:

Sub Test()

Dim s As String: s = "ABCd"

Debug.Print Evaluate(Replace("=SUM(LEN(""X"")-LEN(SUBSTITUTE(""X"",CHAR(ROW(65:90)),"""")))", "X", s))
Debug.Print Evaluate("=SUM(LEN(""" & s & """)-LEN(SUBSTITUTE(""" & s & """,CHAR(ROW(65:90)),"""")))") 'Either of the two.

End Sub

Note: Evaluate will auto-CSE the formula for you. Hence SUM() instead of SUMPRODUCT().

Note: If you are set on using A7 then be sure to be explicit about which worksheet you are working from. By default Evaluate will refer to the then active sheet. E.g.:

Debug.Print Worksheets("Sheet1").Evaluate("=SUM(LEN(A7)-LEN(SUBSTITUTE(A7,CHAR(ROW(65:90)),"""")))")