VBA Val() Fail To Evaluate Negative String Numbers

[Libertarious]

I'm creating an algebraic equation solver.

The equation I have entered into the cell is:

8|x+3|−2=−4|x+3|+8

I've successfully isolated all the terms and coefficients into a Public Type with string members.

When I attempt to evaluate actual values via Val(string), my positive numbers resolve to expected numeric values, while my negative string numbers are resolved to 0.

Relevant Code Excerpt:

Public Function SolveAbsoluteEquation(AbsEquation As String) As String

...

.LeftTerm.AbsoluteTerm.Coefficient = Mid(EquationLeft, 1, DelimiterIndex - 1)
Debug.Print "STR=" & .LeftTerm.AbsoluteTerm.Coefficient
Debug.Print "VAL=" & Val(.LeftTerm.AbsoluteTerm.Coefficient)
.LeftTerm.AbsoluteTerm.ComplexTerm = Mid(EquationLeft, DelimiterIndex, CaptureLenghtIndex + 1)
Debug.Print "STR=" & .LeftTerm.AbsoluteTerm.ComplexTerm
.LeftTerm.UnitTerm = Right(EquationLeft, Len(EquationLeft) - (CaptureLenghtIndex + DelimiterIndex))
Debug.Print "STR=" & .LeftTerm.UnitTerm
Debug.Print "VAL=" & Val(.LeftTerm.UnitTerm)
DelimiterIndex = InStr(1, EquationRight, "|")

.RightTerm.AbsoluteTerm.Coefficient = Mid(EquationRight, 1, DelimiterIndex - 1)
Debug.Print "STR=" & .RightTerm.AbsoluteTerm.Coefficient
Debug.Print "VAL=" & Val(.RightTerm.AbsoluteTerm.Coefficient)
.RightTerm.AbsoluteTerm.ComplexTerm = Mid(EquationRight, DelimiterIndex, CaptureLenghtIndex + 1)
Debug.Print "STR=" & .RightTerm.AbsoluteTerm.ComplexTerm
.RightTerm.UnitTerm = Right(EquationRight, Len(EquationRight) - (CaptureLenghtIndex + DelimiterIndex))
Debug.Print "STR=" & .RightTerm.UnitTerm
Debug.Print "VAL=" & Val(.RightTerm.UnitTerm)
...

End Function

My Debug.Print results are:

STR=8
VAL=8
STR=|x+3|
STR=-2
VAL=0???
STR=-4
VAL=0???
STR=|x+3|
STR=+8
VAL=8

I've tried Trim(string), still fails to properly resolve as expected.

The only way I did get it to resolve was to force my passed input string to the literal: "8|x+3|−2=−4|x+3|+8"

STR=8
VAL=8
STR=|x+3|
STR=-2
VAL=-2
STR=-4
VAL=-4
STR=|x+3|
STR=+8
VAL=8

 

[shg]

Well, let's see if that's true:

Sub x()
    Dim s As String
    
    s = ChrW(&H1806) & 2
    Debug.Print Asc(Left(s, 1))
    s = Replace(s, Chr(63), Chr(45))
    Debug.Print Asc(Left(s, 1))
End Sub

What does the second Print statement display?

 

[Libertarious]

Thanks for the example.

There appears to be a quasi/virtual conversion when the hyphen is passed into a function in Excel.

If I pre-convert the equation via SUBSTITUTE(str,hyphen,dash), then everything works as expected.

Here's another experiment I made to confirm this anomaly:

Debug.Print output results:

dash = Chr(45) = -
hyphen = Chr(63) = ?
test = Chr(63) & 1 = ?1
Val(test) = 0 ???
test = Replace(test, hyphen, dash) = -1
Val(test) = -1 YEAH!

I'm curious why there a ambiguity between the worksheet domain compared to the local VB domain with regard to passing a hyphen through a variable into a function.

(NOTE: I didn't originally generate the equation with a hyphen, this is something that I'm playing with from an online tutorial via copy-paste. I'd never use a hyphen as a negative sign.)

Whenever I debug.print the passed variable, the hyphen is both rendered as a dash in debug.print output, and also registers as Asc(chr$) = 45, which further results with the Replace(passedstring, hyphen, dash) as essentially a null operation. The only time the hyphen/dash anomaly has any effect is when it is processed through the Val() conversion.

Given this ambiguity, it appears that there is no way I can automate the hyphen to dash conversion. I will have to pre-convert the equation prior to sending it to the function.

Thanks for the insight.

 

[Libertarious]

Here's the real source of the ambiguity:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Literal Character:
[/TD]
[TD]Function:
[/TD]
[TD]Output:
[/TD]
[/TR]
[TR]
[TD]?
[/TD]
[TD]=CODE(A2)
[/TD]
[TD]Result=63
[/TD]
[/TR]
[TR]
[TD]−
[/TD]
[TD]=CODE(A3)
[/TD]
[TD]Result=63 ???
[/TD]
[/TR]
[TR]
[TD]-
[/TD]
[TD]=CODE(A4)
[/TD]
[TD]Result=45
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

See the AscW function.