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]

Sure that's a hyphen and not an en dash?

 

[Libertarious]

Sure that's a hyphen and not an en dash?

Yes, the original was a hyphen, and I resolved this before getting to the point of posting this problem:

AbsEquation = Replace(AbsEquation, Chr(63), Chr(45))

NOTE: If you look closely at the Debug.Print output, you'll notice the hyphens are in fact dashes.

Even with the replace function above, I still get improper results.

As indicated in original post, the only time I've gotten the right results is when I force the equation as a literal.

Anything else has proven useless.

 

[Norie]

Can we see the rest of the code?

 

[shg]

AbsEquation = Replace(AbsEquation, Chr(63), Chr(45))

Chr(63) is a question mark, or, maybe more likely, a Unicode character, which Val is unlikely to want any part of.

 

[shg]

Clarification, sorry: Code(many Unicode characters) = 63, which is the ASCII code for a question mark. Depending on where your data is coming from (i.e., other than being typed in), you may have Unicode characters that appear as hyphens.

 

[Libertarious]

The replace function changes Chr(63) regardless how it maps to unicode. Yes Val(Chr(63)) will not result in a numeric evaluation, found that out the instant I started with my function. Again, the Replace() takes this issue completely out of the discussion. Look at the debug.print output, there is no hyphen Chr(63), it is, and I've forced it directly, Chr(45) "-".

Something else is going on...

 

[shg]

You're missing my point. That's one of several Unicode hyphens (&H1806) in A1:

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]᠆[/td][td]A1: Input[/td][/tr]

[tr][td]

2​

[/td][td]

63​

[/td][td]A2: =CODE(A1)[/td][/tr]

[tr][td]

3​

[/td][td]᠆[/td][td]A3: =SUBSTITUTE(A1, CHAR(63), CHAR(45))[/td][/tr]

[tr][td]

4​

[/td][td]

63​

[/td][td]A4: =CODE(A3)[/td][/tr]
[/table]

 

[Libertarious]

Cell A1 Entry:

[TABLE="width: 131"]
<tbody>[TR]
[TD="width: 131"]8|x+3|-2=-4|x+3|+8[/TD]
[/TR]
</tbody>[/TABLE]

Formula entered in adjacent cell A2:

=SolveAbsoluteEquation(A1)

[FONT=courier new]Option Explicit

Public Type CompCoeff
  ComplexTerm As String
  Coefficient As String
End Type

Public Type Terms
  AbsoluteTerm As CompCoeff
  UnitTerm As String
End Type

Public Type Equation
  RightTerm As Terms
  LeftTerm As Terms
End Type

Public Function SolveAbsoluteEquation(AbsEquation As String) As String
 
 Dim DelimiterIndex As Integer
 Dim CaptureLenghtIndex As Integer
 Dim AbsoluteEquation As Equation
 Dim EquationLeft As String
 Dim EquationRight As String
 Dim LeftCoefficientValue As Double
 Dim RightCoefficientValue As Double
 
 SolveAbsoluteEquation = ""
 
 AbsEquation = Replace(AbsEquation, Chr(63), Chr(45)) '"8|x+3|-2=-4|x+3|+8"
 
 DelimiterIndex = InStr(1, AbsEquation, "=")
 
 EquationLeft = Left(AbsEquation, DelimiterIndex - 1)
 
 EquationRight = Right(AbsEquation, Len(AbsEquation) - DelimiterIndex)
 
'8|x+3|-2=-4|x+3|+8

 With AbsoluteEquation
   
   DelimiterIndex = InStr(1, EquationLeft, "|")
   
   CaptureLenghtIndex = InStr(DelimiterIndex + 1, EquationLeft, "|") - DelimiterIndex
  
  .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, "|")
   
   CaptureLenghtIndex = InStr(DelimiterIndex + 1, EquationRight, "|") - DelimiterIndex
  
  .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)
  'Subtract right abs coefficient from the left abs coefficient
  'Set to left abs coefficient:
   
   LeftCoefficientValue = Val(.LeftTerm.AbsoluteTerm.Coefficient)
   Debug.Print .LeftTerm.AbsoluteTerm.Coefficient
   Debug.Print LeftCoefficientValue
   RightCoefficientValue = Val(.RightTerm.AbsoluteTerm.Coefficient)
   Debug.Print .RightTerm.AbsoluteTerm.Coefficient
   Debug.Print RightCoefficientValue
  .LeftTerm.AbsoluteTerm.Coefficient = Val(LeftCoefficientValue - RightCoefficientValue)
   Debug.Print .LeftTerm.AbsoluteTerm.Coefficient
   
   SolveAbsoluteEquation = .LeftTerm.AbsoluteTerm.Coefficient & _
                           .LeftTerm.AbsoluteTerm.ComplexTerm & _
                           .LeftTerm.UnitTerm & _
                           "=" & _
                            Val(.RightTerm.UnitTerm)
   
 End With

End Function

[/FONT]

 

[Libertarious]

You missed mine:

AbsEquation = Replace(AbsEquation, Chr(63), Chr(45))

Chr(63), CODE(63) is changed to Chr(45).