evaluate a negative number

Fordraiders
Fordraiders used Ask the Experts™
on
excel 2010 vba

The data is being grabbed off a worksheet

Trying to catch a negative number and do stuff with it..

Dim CellDataEvaluate As String
' for example
CellDataEvaluate =  -0.82
 


' catch a negative number
 If IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") And InStr(1, CellDataEvaluate, "-") < 0 Then   ' this should catch a negative string
                             CellDataEvaluate = CDec(CellDataEvaluate)

The line above is not catching a negative number ?


Thanks
fordraiders
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Dim CellDataEvaluate As Currency

Author

Commented:
martin , the value showing in the cell is  -0.82%   the text value is -0.82....
my fear is the text then may be   at some point .       .-0.82%   ??

Thanks
fordraiders
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I believe you'll be OK.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
well, i guess i could strip the % sign anyway , just in the code temporarily ?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Test this little macro.
Sub test()

Dim CellDataEvaluate As Currency
' for example
CellDataEvaluate = -0.82
 

If CellDataEvaluate < 0 Then
    MsgBox "Negative"
    MsgBox CellDataEvaluate + 5
End If
End Sub

Open in new window

The problem with your code is the
IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") And InStr(1, CellDataEvaluate, "-") < 0

Open in new window

Will never return anything smaller than 0 (zero), Isnumeric returns true or false, Instr returns the position of the character if found, IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") is like "if true and zero" which will always be ZERO, then you add another
And InStr(1, CellDataEvaluate, "-") < 0   this <0 only applies to the last INSTR not to both of them and also will never be anything smaller than ZERO.

After you confirm that it IS numeric then you
only need to check for CellDataEvaluate<0
no need for the sign checking
Dim CellDataEvaluate As String
' for example
CellDataEvaluate =  -0.82
 


' catch a negative number
 If IsNumeric(CellDataEvaluate) Then
   if CDec(CellDataEvaluate)< 0 Then   ' this should catch a negative string
      Do Stuff
end if
end if

Open in new window

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
If the cells contain +ve and -ve percentage values, only cell with 100% will contain the actual value 1 underneath the cell else all the percentage values whether +ve or -ve will be less than 0. So you cannot check them for <0.
Instead read the Text of the cell like below...

Assuming your percentage value -0.82% is in A2, then following line will detect if it was -ve percentage value.

If IsNumeric(Range("A2").Value) And InStr(Range("A2").Text, "-") > 0 Then
    MsgBox "Negative"
End If

Open in new window

See if you can incorporate this into your code.
Retired Senior Systems Analyst/Programmer
Commented:
How about along these lines (This Function "IsNegative()" can be used in VBA and VB6)
The "type" of the variable passed in these numerous calls to IsNegative vary (Several calls, just to test it out)..
The first call is using a string stored in a variable within the VB code (i.e. not actually on the spreadsheet)..
The second is using the contents of a numeric in cell "E5", containing, say,  1.70)
The third call is using the contents of a string in a cell "E6" containing, say, "-1.70" (with inverted commas around the value)
The fourth call is using the contents of a currency value stored in cell "E7" (formatted as currency) containing say "$125.32"

Change the values after you run the macro to different values (including negative currency in Cell E7). It still works!

Data in spreadsheet awaiting call to "IsNegative"
First place the above data into a spreadsheet, placing values in cells E5, E6 and E7 as shown (remember to format cell E7 as currency)
Then add the following code to a module in the spreadsheet.
Finally either step through the code using the VBA debugger, or simply run it (run macro named "Main")

A workbook containing all this data and the macro is attached.
Sub Main()
   Dim CellDataEvaluate As String
   Range("E1").select
   CellDataEvaluate = "-0.82"
   MsgBox ("Code has assigned a string value to CellDataEvaluate of -0.82" & vbCrLf & vbCrLf & _
           "The value for CellDataEvaluate is " & IIf(isNegative(CellDataEvaluate), "Negative", "Positive"))
   Range("E5").Select
   MsgBox ("The value in cell E5 is " & IIf(isNegative(Range("E5")), "Negative", "Positive"))
   Range("E6").Select
   MsgBox ("The value in cell E6 is " & IIf(isNegative(Range("E6")), "Negative", "Positive"))
   Range("E7").Select
   MsgBox ("The value in cell E7 is " & IIf(isNegative(Range("E7")), "Negative", "Positive"))
End Sub

Public Function isNegative(varVal As Variant) As Boolean
  'Note: This code will fail the test on a numeric expressed in hex format (will always return false)
  ' so do not use it as a test on a Hex value.
  Dim nVarType As Integer
  Dim nVal As Long
  nVarType = VarType(varVal)
  Select Case nVarType
    Case vbNull, vbDate, vbObject, vbError, vbVariant, vbDataObject, vbUserDefinedType, vbArray
      isNegative = False
    Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte, vbVariant
      isNegative = varVal < 0
    Case vbString
      Dim strCurr As String
      strCurr = "$" 'could test for other currency symbols by adding extra currency symbols to this string
      If InStr(varVal, ".") > 0 Or InStr(strCurr, Left(Trim(varVal), 1)) > 0 Then
         If InStr(strCurr, Left(Trim(varVal), 1)) > 0 Then
           nVal = CCur(varVal) 'Remember,even a currency value can be negative!
         Else
           varVal = Replace(varVal, Chr(34), "") 'strip out surrounding strings
           If IsNumeric(varVal) Then
               nVal = Val(varVal)
           Else
               nVal = 1 'Forcing a false into "IsNegative" since this "mixed" value does not represent a true number
           End If
         End If
      Else
         nVal = CLng(varVal)
      End If
      isNegative = (nVal < 0)
    Case Else
      MsgBox "Unknown value passed to IsNegative() function"
  End Select
End Function

Open in new window

TestForNegativeValueInExcel.xlsm

Author

Commented:
Thanks all, Testing suggestions


Martin, Just trying your bit:
Not Working when evaluating this code before the If Then.
  lastColumn = 11
            While Len(.Cells(lastRow, 1)) > 0
                lastRow = lastRow + 1
            Wend
            lastRow = lastRow - 1
            For i = 2 To lastRow
                For j = 1 To lastColumn
                    CellDataEvaluate = Trim(.Cells(i, j).Value)    <---    error here type mismatch
                    CellDataEvaluate = Replace(CellDataEvaluate, "%", "")
                    CellDataEvaluate = Replace(CellDataEvaluate, "$", "")
                    CellDataEvaluate = Replace(CellDataEvaluate, "#", "")
                    CellDataEvaluate = Trim(CellDataEvaluate)
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't believe you need any of the last five rows because when CellDataEvaluate is defined as currency it's all done for you. Try it and let me know if something doesn't work after those lines are removed.

Author

Commented:
just a side note.

Is there a way to actually check the  cell formatting for a given cell ?
General, Text, percentage,  custom   ?

 Trim(.Cells(i, j).Value)    for this   cell range ?

Thanks

Author

Commented:
Chris, Just to clarify.
Your code is actually telling me that  regardless of cell formatting, what the actual cell value is ? a true number or not ?
varVal  is the variant being tested ?

The reason i ask is because if a cell happens to contain an alpha character, i need to handle it..
     End If
      Else
         nVal = CLng(varVal) '  <   ------------   ERROR HERE IF THE CELLS CONTAINS alpha characters ?
      End If
      isNegative = (nVal < 0)
    Case Else
      MsgBox "Unknown value passed to IsNegative() fucntion"
Chris RaisinRetired Senior Systems Analyst/Programmer

Commented:
Yes, it will test for any type of value passed to it. A variant can hold any variable type. Remember, this code can be used in VB6 or VBA not just within a spreadsheet and so other variable types could be used in VBA apps.

With Excel of course the values are usually either a number or a string. I do not think that the formatting of the cell actually makes any difference, but I had to incorporate every possibility in order to make this a useful function for not just Excel.

Cheers
Chris

Author

Commented:
Chris, Thanks for the response , but what about the alpha character error. and how to handle ?
fordraiders
Chris RaisinRetired Senior Systems Analyst/Programmer

Commented:
OK, I missed the possibility of "Nothing" being passed (an empty cell) and also a cell that has alpha characters, so I tweaked the code to handle that, plus I have given the user the chance to place whatever values they like in any Cell in Range ("E1") through to Range("E10")
before running the macro. (For testing purposes).

Give that a try.

If all values process without error, then the function "IsNegative" in the code is the one to use.

The revised Workbook is attached and the code is now:

Option Explicit

'(This Function  can be used in VBA and VB6. It returns an indication (True or False) whether a value passed to it (of any type)
'is positive or negative. It logically only  applies to numbers or currency values, all other types return Positive (i.e. "Not Negative") 

Sub Main()
   Dim CellDataEvaluate As String
   Dim strAddress As String
   Dim x As Integer
   Range("A1").Select
   CellDataEvaluate = "-0.82"
   If MsgBox("Place various values in column " & Chr(34) & "E" & Chr(34) & " in rows 1 to 10", vbDefaultButton1 Or vbOKCancel) = vbCancel Then
     Exit Sub
   End If
   MsgBox ("Code has assigned a string value to variable 'CellDataEvaluate' of -0.82" & vbCrLf & vbCrLf & _
           "The value for CellDataEvaluate is " & IIf(isNegative(CellDataEvaluate), "Negative", "Positive"))
   For x = 1 To 10
     strAddress = "E" & CStr(x)
     Range(strAddress).Select
     If MsgBox("The value in cell " & strAddress & " is " & IIf(isNegative(Range(strAddress)), "Negative", "Positive"), vbDefaultButton1 Or vbOKCancel) = vbCancel Then
       Range("A1").Select
       Exit For
     End If
   Next
   MsgBox "Evaluation complete"
End Sub

Public Function isNegative(varVal As Variant) As Boolean
  'Note: This code will fail the test on a numeric expressed in hex format (will always return false)
  ' so do not use it as a test on a Hex value.
  Dim nVarType As Integer
  Dim nVal As Long
  nVarType = VarType(varVal)
  Select Case nVarType
    Case vbNull, vbDate, vbObject, vbError, vbVariant, vbDataObject, vbUserDefinedType, vbArray
      isNegative = False
    Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte, vbVariant
      isNegative = varVal < 0
    Case 0    'a blank cell
      isNegative = False
    Case vbString
      Dim strCurr As String
      strCurr = "$" 'could test for other currency symbols by adding extra currency symbols to this string
      If InStr(varVal, ".") > 0 Or InStr(strCurr, Left(Trim(varVal), 1)) > 0 Or InStr(strCurr, Right(Trim(varVal), 1)) > 0 Then
         If InStr(strCurr, Left(Trim(varVal), 1)) > 0 Then
           nVal = CCur(varVal) 'Remember,even a currency value can be negative!
         Else
           varVal = Replace(varVal, Chr(34), "") 'strip out surrounding strings
           If IsNumeric(varVal) Then
               nVal = Val(varVal)
           Else
               nVal = 1 'Forcing a false into "IsNegative" since this "mixed" value of characters and numbers does not represent a true number
           End If
         End If
      Else
         If IsNumeric(varVal) Then
           nVal = CLng(varVal)
         End If
      End If
      isNegative = (nVal < 0)
    Case Else
      MsgBox "Unknown value passed to IsNegative() function"
  End Select
End Function

Open in new window

TestForNegativeValueInExcel.xlsm

Author

Commented:
THANKS ALL
Chris RaisinRetired Senior Systems Analyst/Programmer

Commented:
So who has been awarded points for helping?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial