Converting a String into a Single

Question

My question might sound stupid but I tried several solutions exposed on internet without any success. I'm totally new to VBA.

Problem: I am extracting data from a txt file. That data is stored into a String and I need to store that value into a Single variable.

Context: I am using Microsoft Excel 2010, the code is done under Microsoft Visual Basic for Applications.

Solutions that i tried: In all these cases, valueToRead is equal to "1.580000".

1) realAngle = CSng(valueToRead) I get the execution error '13', type incompatibility

2) realAngle = Single.Parse(valueToRead) Compilation error, syntax error

3) realAngle = Single.Parse(valueToRead, System.Globalization.NumberFormatInfo.InvariantInfo) Compilation error, syntax error

4) realAngle = Convert.ToSingle(valueToRead) Execution error '424': object required

My code is below :

Sub macro_test()
' File to read data from
Dim logFile As String
' Variable containing a line from the file
Dim textLine As String
' Variable containing a part of the line
Dim ReadValue As Variant
' Variable containing a  part of ReadValue
Dim ReadValue2 As Variant
' Desperate variable in order to be sure to have a String value
Dim valueToRead As String
' Angle value stored in the file
Dim realAngle As Single
' Number of elements separated by " | " in the lien
Dim Size As Integer

' Initialize variables
Size = 0
realAngle = 0
logFile = "FilePathAndName"

' Open the txt file
Open logFile For Input As #1

' Read until the end of the file
Do Until EOF(1)
    ' Get a line of text from the file
    Line Input #1, textLine
    ' Split the line with " | " separator
    ReadValue = Split(textLine, " | ")

    ' Count the number of elements
    Size = UBound(ReadValue) - LBound(ReadValue) + 1

    ' If the line have enough elements then it may be of interest
    If Size > 9 Then
        ' if this is the correct line thanks to a correct identificator
        If ReadValue(3) = "MyIdentificator" Then
        ' Split the line with the " = " sign
        ReadValue2 = Split(ReadValue(8), " = ")
        ' Storing the value into a String
        valueToRead = ReadValue2(1)

        realAngle = CSng(valueToRead)
        'realAngle = Single.Parse(valueToRead)
        'realAngle = Convert.ToSingle(valueToRead)
        'realAngle = Single.Parse(valueToRead, System.Globalization.NumberFormatInfo.InvariantInfo)
        End If
    End If
Loop

Close #1
End Sub

Thank you in advance for your help!

edit: Here is an exemple of the line that I get in the file: Log Level: LogLevel | File name: X:\Folder1\Folder2\Folder3\Folder4\Folder5\Folder6\FileName.h | Function name: FunctionName| Line number: XXXX | Information: MYINFO | BLABLA | VALUE1 = 32768 | VALUE2 = 0.000000 | VALUE3 = 1.580000 | VALUE4 = 0.000000 | VALUE5 = 3581.941895 | VALUE6 = 36349.941406

I am currently trying to get VALUE3.

Answer 1

Just guessing that you are not in USA/UK. In Germany, you have , and not . as a regional setting. Thus, add this to the string, before casting:

Public Function change_commas(ByVal myValue As Variant) As String

    Dim str_temp As String

    str_temp = CStr(myValue)
    change_commas = Replace(str_temp, ",", ".")

End Function