evaluate a negative number

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
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Dim CellDataEvaluate As Currency
FordraidersAuthor 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 dirtCommented:
I believe you'll be OK.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

FordraidersAuthor Commented:
well, i guess i could strip the % sign anyway , just in the code temporarily ?
Martin LissOlder than dirtCommented:
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

aranaCommented:
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 ExpertCommented:
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.
Chris Raisin(Retired 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor 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 dirtCommented:
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.
FordraidersAuthor 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
FordraidersAuthor 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 Raisin(Retired 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
FordraidersAuthor Commented:
Chris, Thanks for the response , but what about the alpha character error. and how to handle ?
fordraiders
Chris Raisin(Retired 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
FordraidersAuthor Commented:
THANKS ALL
Chris Raisin(Retired Analyst/Programmer)Commented:
So who has been awarded points for helping?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.