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

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.

CodePudding user response：

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)

CodePudding user response：

Yes, you can convert strings containing numeric text to a number datatype, like integer, double, or long depending on your data and needs, and you can do so without making any changes at all to the spreadsheet or the data within each cell. I make no judgment as to the wisdom of altering or not altering the datatype of the underlying data.

Without knowing more about how your code manipulates your data, I think the easiest way to do this would be to load your data into an array, then test each element in the VBA array (cell in the spreadsheet range) using the worksheet function IsNumber. If the element in the VBA array (cell in the spreadsheet range) is not a number, and if you know there are no genuine text strings in your data, only numbers formatted as strings instead of numbers formatted as numbers, then you can convert the string using the Val function. Using the Val function instead of one of the other datatype conversion functions gives you a little bit more flexibility but also introduces some ambiguity at the same time.

If you are confident that your data is uniform when it comes to decimal places, floating points, not larger than X nor smaller than Y, etc., and if it is important that your string text be converted to a specific number datatype, you should use the specific conversion function that matches your data to avoid any potential runtime problems.

Here is an example that takes a spreadsheet containing the data in your screenshot (C1 through C3 because I had to assume row numbers) and converts text strings to numbers as needed. I've included some extra steps and comments to help explain the code for readers of different skill levels. This code is tested and working in Office 365 Excel Version 2204 using the data from your screenshot. YMMV.

Option Explicit

Sub TestIfNumber()

'Create a range containing your data
Dim rngMyData As Range
    Set rngMyData = ThisWorkbook.Worksheets("Sheet1").Range("C1:C3")

'Add your range's data to a simple array to avoid manipulating your actual data
Dim arrMyArray() As Variant
    arrMyArray = rngMyData.Value
   
'Test each value in the array to determine if it is a number.
'If it isn't a number, then use the Val function to convert what we assume is a string
'to a number for use with your math or sorting functions. Be careful with conversions
'like this because they will strip leading zeroes and all spaces, and will use the locale
'of your Excel installation to decide what to do with . and , in a given string.
Dim lngRowIndex As Long
Dim strMyCellString As String

For lngRowIndex = LBound(arrMyArray, 1) To UBound(arrMyArray, 1)
    If WorksheetFunction.IsNumber(arrMyArray(lngRowIndex, 1)) = False Then
        Debug.Print "Element from (" & lngRowIndex & ", 3) is not a number"
        strMyCellString = rngMyData(lngRowIndex, 1).Value
        arrMyArray(lngRowIndex, 1) = Val(strMyCellString)
        'now check again after conversion (you can cut this next if test, it's just a demo)
        If WorksheetFunction.IsNumber(arrMyArray(lngRowIndex, 1)) = True Then
            Debug.Print "Element from (" & lngRowIndex & ", 3) was converted to number"
        Else
            Debug.Print "something weird happened - check your original data"
        End If
    ElseIf WorksheetFunction.IsNumber(arrMyArray(lngRowIndex, 1)) = True Then
        Debug.Print "Element from (" & lngRowIndex & ", 3) is already a number"
    End If
Next lngRowIndex

End Sub

If your goal is actually to modify the underlying data to allow regular users and VBA code alike to sort using numbers, then you can either

A) continue using the array from my example code above but replace arrMyArray(lngRowIndex, 1) = Val(strMyCellString) with rngMyData(lngRowIndex, 1) = Val(strMyCellString) and then once all values in the array have been checked and converted as needed, execute your existing code on the array.

B) skip the array and run the sub against the data range itself by incorporating some version of the following into/before your existing code:

Sub TestIfNumber()
Dim rngMyData As Range
    Set rngMyData = ThisWorkbook.Worksheets("Sheet1").Range("C1:C3")

Dim rngCell As Range
    For Each rngCell In rngMyData.Cells
        If WorksheetFunction.IsNumber(rngCell) = False Then
            rngCell = Val(rngCell) 'same warnings re number datatypes and data uniformity apply
        End If
    Next rngCell
End Sub