CODEWITHSUNDEEP
excelvba
user618677
user618677

Reputation: 5069

Reading negative value from cells

The question is this .. When ActiveSheet.Cells(14, 5).Value is something like -1.3 my code fails ... What is causing this?

Function TestCell() As Integer
    Dim Boon As Integer
    Dim nextBoon As Integer
    Dim nextTwo As Integer
    Dim nextOne As Integer
    Dim nextThree As Integer

    Boon = ActiveSheet.Cells(14, 5).Value
    nextBoon = ActiveSheet.Cells(14, 6).Value
    nextTwo = ActiveSheet.Cells(14, 7).Value
    nextOne = ActiveSheet.Cells(14, 8).Value
    nextThree = ActiveSheet.Cells(14, 9).Value
    If Boon <= 1.8 And Boon >= -1.8 Then
        If nextBoon <= 1000 And nextBoon >= -1000 Then
            If nextTwo <= 0.36 And nextTwo >= -0.36 Then
                If nextOne <= 0.13 And nextOne >= -0.13 Then
                    If nextThree <= 1.2 And nextThree >= -1.2 Then
                        TestCell = 1
                    Else
                        TestCell = 0
                    End If
                Else
                    TestCell = 0
                End If
            Else
                TestCell = 0
            End If
        Else
            TestCell = 0
        End If
    Else
       TestCell = 0
    End If
End Function

Upvotes: 0

Views: 133

Answers (2)

Gaijinhunter
Gaijinhunter

Reputation: 14685

As JMax said, use Double instead of Integer for Boon. I really wanted to give you 2 bits of advice since your code is 2x the length it could be:

First, don't use else statements. Just declare that the testcell is 0 and do your if-thens to see if you can change it to 1 or not.

Second, if you are only using a cell 2 times, there is no particular benefit to store it as a variable (on the flip-side you lose readability). Just use "cells(15, 5).value", etc. You shouldn't specify activesheet either - by default it uses the activesheet.

These 2 tips should help you out in the long run and it's good practice to follow.

Update:

Allow me to introduce a much faster, more effecient way to do this. You pass it in a 5 cell range. Doing it this way, you can just drag the formula all the way down your column and it'll work for every cell.

In your example, you'd call it by using:

=TestCell(E14:I14)

Function TestCell(ByVal myRange As Range) As Long

Dim vArray As Variant
Dim result As Long
result = 0

vArray = myRange.Value

If vArray(1, 1) <= 1.8 And vArray(1, 1) >= -1.8 Then
    If vArray(1, 2) <= 1000 And vArray(1, 2) >= -1000 Then
        If vArray(1, 3) <= 0.36 And vArray(1, 3) >= -0.36 Then
            If vArray(1, 4) <= 0.13 And vArray(1, 4) >= -0.13 Then
                If vArray(1, 5) <= 1.2 And vArray(1, 5) >= -1.2 Then
                    result = 1
                End If
            End If
        End If
    End If
End If

TestCell = result

End Function

How it works: The 5 cell range is converted to a variant array (which can hold Integers, strings, doubles, whatever). The checks are done using the varray since it's insanely fast, effecient, and you don't need to worry about data types (one of the major benifits is the ability to dump an entire range into a vArray as you see I do in my code). Since we set the result at 0, we don't need any else statements, you just do the if-thens to see if the value can be changed to 1 or not.

Using this method, the calculations can be done in microseconds, and the function updates itself everytime one of the 5 cells is changed, so you have real-time results.

Upvotes: 3

JMax
JMax

Reputation: 26591

Your Boon is declared as an Integer>> http://msdn.microsoft.com/fr-fr/library/06bkb8w2(v=vs.80).aspx

So Boon = ActiveSheet.Cells(14, 5).Value will return -1 if the ActiveSheet.Cells(14, 5).Value is -1.3

You need to use Double if you want it to work. 

Function TestCell() As Integer
    Dim Boon As Double

Upvotes: 3

Related Questions