Any Excel Visual Basic Basic People out there?

[Peter Stahl]

I writing a macro in Visual Basic in MS Excel and want to be able to determine iif a number is Odd or Even. Is there a function that will do this? Also can you terminate/stop a macro that is running, I use the MsgBox function to help see what's going on but if I stick it in a loop that goes on for a while I feel like I'm doing morse code with the mouse. Also are there any web sites with MS VB help?

thanks, Pete

[Mark Cothren]

I haven't done VB in years... I'm on my way out the door or I'd play around with this a while. I did a quick search and found this code, which isn't exactly what you want to do but might help a bit.


The event procedure that requests an odd number.

1: Sub cmdOdd_Click ()

2: ' Request an odd number

3: Dim OddStr As String

4: Dim OddNum As Integer

5: Do

6: OddStr = InputBox$("Enter an odd number", "Get Odd")

7: If (OddStr
= "") Then ' Quit if pressed Cancel

8: Exit Do ' Quits the Do loop early

9: End If

10: OddNum = Val(OddStr) / 2

11: ' The integer OddNum holds the exact value

12: ' of the Val(OddStr) / 2 if OddNum is even

13: Loop Until (OddNum <>
(Val(OddStr) / 2))

14: End Sub


Descriptions



1: The command button's Name property contains cmdOdd, so the name of the Click event procedure is cmdOdd_Click().


2: A remark explains the purpose of the procedure.


3: This line defines a string variable that will hold the result of the InputBox$() function.

4: This line defines an integer variable that will hold the result of the InputBox$() function.


5: This line begins a loop.

6: Here the program collects the user's response to the request for an odd number, and it displays an appropriate input box title. Use InputBox$() to get strings from the user.


7: If the user pressed the Cancel command button, the program exits the loop.

8: This line terminates the Do loop.


9: This line is the end of the body of the If statement.

10: This line converts the input string to a number, divides the number by two, and stores the result in an integer variable.


11: A multiline remark describes the check for an odd number.

12: This line continues the remark.


13: OddNum holds an integer value calculated and stored in line 10. If integer OddNum variable equals the decimal calculation of the user's response divided by two, the user entered an even number, and the program needs to loop again and request the odd number once more.
14: This line terminates the event procedure.

[Lars Thomas]

This will do it:

Sub Even_Odd()

Range("A1").Select

Remainder = Selection Mod 2

If Remainder = 0 Then
MsgBox "The number is EVEN"
Else: MsgBox "The Number is ODD"
End If

End Sub

You can use F8 to step through it, or add breakpoints by clicking F9 on the line you want it to stop at. Although I often use your method of displaying a value in a msgbox during development.

[Dennis Peacock]

Wow....You Arkies are purdy Smart Fellers!!!!
Oh, and you too Lars.

[Lars Thomas]

Well, I've been to Arkansas - and even ate at Doe's in LR.

[Peter Stahl]

Mark and Lars,

Thanks for the quick reply. I knew there had to be a easy way to do it. I used Lars method as it seemed simpler and less code. As far as steping though a macro I don't remeber how to do it, any tips?

thanks again

[Doug Shepard]

Just FYI - there's a much more efficient way to figure out the odd/even status than using the Mod operator. Unless VB is internally special casing divisors of 2, it's using division to divide the number and return the remainder. Divisions are usually one of the most CPU-cycle intensive operations. If you look at the number in binary form, all odd numbers will have the LSB (least significant bit) set. Because of the way negative numbers are stored - Two's Complement, where you flip all the bits and add 1, this is still true for negative numbers. VB will let you do a bitwise-AND operation to compare numbers bit by bit. So all you really have to do is check if the LSB is set - if so, it's odd.

This example is also relying on VB's conversion of 0 to a Boolean FALSE, and 1 to a Boolean TRUE

Function IsOddNumber(theNumber As Integer) As Boolean
IsOddNumber = theNumber And 1
End Function

(I'm at work, bored, and have nothing better to do with my time )

[Peter Stahl]

Just FYI - there's a much more efficient way to figure out the odd/even status than using the Mod operator. Unless VB is internally special casing divisors of 2, it's using division to divide the number and return the remainder. Divisions are usually one of the most CPU-cycle intensive operations. If you look at the number in binary form, all odd numbers will have the LSB (least significant bit) set. Because of the way negative numbers are stored - Two's Complement, where you flip all the bits and add 1, this is still true for negative numbers. VB will let you do a bitwise-AND operation to compare numbers bit by bit. So all you really have to do is check if the LSB is set - if so, it's odd.

This example is also relying on VB's conversion of 0 to a Boolean FALSE, and 1 to a Boolean TRUE

Function IsOddNumber(theNumber As Integer) As Boolean
IsOddNumber = theNumber And 1
End Function

(I'm at work, bored, and have nothing better to do with my time )

I should have also mentioned what I was using this function for. We have a shift calendar at work and was adding to the macro used to create it and only needed to know which were even or odd months. So I don't think the Mod operator will slow this thing down much. I'll post a picture of it if I can get one small enough (kb wise) to post. Doug, thanks for help. I need a book with all the function and such for Excel VB.

[Lars Thomas]

Peter, from Excel, Hit Alt-F11. Your macro may display or you may need to find it in the project explorer. It will be in one of the 'Module' Directories. From your macro, click F8 to step through. You will need to repeatedly hit F8 for every line. Hoover your mouse over any variable to see their values.

Theoretically, you don't need to step through the entire macro. Go to the part you are troubleshooting and hit F9 (Toggle Breakpoint). The code will run normally to that point and then stop. From that point, start hitting F8 to step through. Once you are through the trouble spot, F5 will run it normally (at least to the next Breakpoint.)

[Peter Stahl]

Peter, from Excel, Hit Alt-F11. Your macro may display or you may need to find it in the project explorer. It will be in one of the 'Module' Directories. From your macro, click F8 to step through. You will need to repeatedly hit F8 for every line. Hoover your mouse over any variable to see their values.

Theoretically, you don't need to step through the entire macro. Go to the part you are troubleshooting and hit F9 (Toggle Breakpoint). The code will run normally to that point and then stop. From that point, start hitting F8 to step through. Once you are through the trouble spot, F5 will run it normally (at least to the next Breakpoint.)

Lars,

Thanks again. I'll have to put this tip somewhere so I know where to find it next time I need it.

[Lars Thomas]

Any time. Happy to help. I love that stuff. Lars