Converting string values in range reference to numeric values for use in VBA

Question

I have a VBA User defined function which takes multiple range references for using it in the code. My problem is when I try to use a particular range reference which has numbers as seen below that are in string format:

I cannot do any numeric operations like sorting the range or ranking it etc. How can I convert the range reference values from string to numeric within the code without making any changes on the excel sheet. Can I make a duplicate copy of the range and convert that new range into integer/numeric.

My table is as below:

The VBA Code that I am using to derive the Column5 is below

1. Function myFunction (Column1Range as Range, Column2Range as Range, Column3Range as Range, Column4Range as Range, Column1Value As String, Column2Value As String, Column3Value As String, Column4Value As String)
2. Dim FirstLevelCount, SecondLevelCount, ThirdLevelCount As Integer
3. FirstLevelCount = WorksheetFunction.CountIf(Column1Range, Column1Value)
4. SecondLevelCount = WorksheetFunction.CountIfs(Column1Range, Column1Value, Column2Range, Column2Value)
5. ThirdLevelCount = WorksheetFunction.CountIfs(Column1Range, Column1Value, Column2Range, Column2Value, Column3Range, CDbl(CDate(Column3Value)))
6. ThirdLevelSequencing = WorksheetFunction.CountIfs(Column1Range, Column1Value, Column2Range, Column2Value, Column3Range, "<" & CDbl(CDate(Column3Value))) + 1
7. FourthLevelCount = WorksheetFunction.CountIfs(Column1Range, Column1Value, Column2Range, Column2Value, Column3Range, CDbl(CDate(Column3Value)), Column4Range, Column4Value)
8. FourthLevelSequencing = WorksheetFunction.CountIfs(Column1Range, Column1Value, Column2Range, Column2Value, Column3Range, CDbl(CDate(Column3Value)), Column4Range, "<" & Column4Value) + 1
9. If FirstLevelCount = 1 Then
    myFunction = "NA"
10. End If
11. If SecondLevelCount = 1 Then
12.     myFunction = "NA"
13. ElseIf SecondLevelCount > 1 Then
14.     If ThirdLevelCount > 1 Then
15.        myFunction = ThirdLevelSequencing & "." & FourthLevelSequencing
16. ElseIf ThirdLevelCount = 1 Then
17.    myFunction = ThirdLevelSequencing
18. End If
19. End Function

I am getting the same values for Rows 2 & 3 (Excel Picture) in Column5 when actually I should be getting the values as seen in the table picture. The problem is on Row 8 (VBA Code) where the countif has a reference to the Column4Range range reference which is actually a column with string values, but needs to be converted to a integer so that numbering in Column5 can be done basis the lowest to largest. The values that I get for Rows 2 & 3 (Excel Picture) is "2.1" when I should actually get the values as shown in the picture.

Answer 1

Solution
You do not need to convert them in another range. Whenever you need to "convert" a variable into another you have the conversion functions.
For this specific scenario, assuming that your function expects a double, you would need to use something like

Function myudfsum (arg1 as Variant) as Double

myudfsum = CDbl(arg1) * 6

End Function

If you need to sort them for example, you'd need to iterate over each element and convert each one of them, however, if that is the case, the conversion should be in the original data, assuming the numbers should be threated as numbers (otherwise may be confusing to the user why s/he's unable to sort like the macro does)