VBA Odd Numbers/Mod

Question

I'm looking for some help with my VBA script. I'm stuck on trying to figure out how to use the mod function.

This is what I've done so far:

Function AddOddNumbersWithMod(nr)
    Dim i, sum
    sum = 0
    For i = (IF 1 MOD 2 = 0) to nr step 1
        sum = sum + i <-- (calculate all the odd numbers before nr)
    Next i
End Function

Any advice would be greatly appreciated.

Please learn to format your code when posting here. You can find out how by clicking the *?* button at the top right of the area where you're typing your post. (And you have an obvious error in your code - look at the condition in the () after `For i =` and see if you can spot it.) — Ken White, Feb 23 '17 at 23:11
`If (1 Mod 2) = 0 ` will always be `False` or `0`. Why would you put a calculation in an `If` statement using nothing but fixed, magic numbers, that will always evaluate to the same answer? Just use `For i = 0 to nr` and find some other, _useful_ place to learn how to use `MOD`. — FreeMan, Feb 24 '17 at 12:37
Rolled back - question was removed and replaced with just __This is what I've done so far:__ — Darren Bartrup-Cook, Feb 27 '17 at 10:47

John Coleman · Answer 1 · 2017-02-23T23:43:56.303

8

For completeness sake, here is a loop-free version:

Function SumOfOdds(n As Long) As Long
    'returns sum 1 + 3 + 5 + ... of all odd numbers <= n
    SumOfOdds = Int((n + 1) / 2) ^ 2
End Function

Based on this picture:

The L-like shapes contain successive odd numbers and nicely fit together to form perfect squares. This nice pattern was well-known to the ancient Greeks.

edited Feb 23 '17 at 23:43

answered Feb 23 '17 at 23:42

John Coleman

51,337
7
54
119

2

I was trying to work out the equation, that truly is a thing of beauty :) – Dan Donoghue Feb 23 '17 at 23:43
2

Very instructive, hats off. – A.S.H Feb 23 '17 at 23:52
1

Poetry of a Math Teacher :) – Shai Rado Feb 24 '17 at 02:47

Gordon Bell · Answer 2 · 2017-02-23T23:26:46.483

Function AddOddNumbersWithMod(nr)
    Dim i As Long, sum As Long
    sum = 0
    For i = 1 To nr - 1 Step 1
        If (i Mod 2 <> 0) Then
            sum = sum + i  ' <-- (calculate all the odd numbers before nr)
        End If
    Next i

    AddOddNumbersWithMod = sum
End Function

To add odd numbers without Mod, you can use Step 2 to skip even numbers starting from 1.

Function AddOddNumbersWithoutMod(nr)
    Dim i As Long, sum As Long
    sum = 0
    For i = 1 To nr - 1 Step 2
        sum = sum + i  ' <-- (calculate all the odd numbers before nr)
    Next i

    AddOddNumbersWithoutMod = sum
End Function

score 2 · Answer 3 · answered Feb 23 '17 at 23:20

You want the result of i Mod 2 to be 1, it shows the remainder and if you are using 2 as your divisor you want a remainder of 1 to show an odd number. For example 7 mod 2 = 1, this is because 7 divided by 2 equals 3 with a remainder of 1, it's that remainder that we are interested in.:

Function AddOddNumbersWithMod(nr)
    Dim i As Double, MySum As Double
    For i = 0 To nr
         If i Mod 2 = 1 Then MySum = MySum + i ' <-- (calculate all the odd numbers before nr)
    Next i
    AddOddNumbersWithMod = MySum
End Function

score 0 · Answer 4 · edited Oct 18 '18 at 15:39

0

Sub Oddeven()
  Dim x As Double
  x = InputBox("Please enter the Number: ")
  If x Mod 2 = 0 Then
    MsgBox ("The Number is the Even Number")
  Else
    MsgBox ("The Number is the Odd Number")
  End If
End Sub

edited Oct 18 '18 at 15:39

giusti

3,156
3
29
44

answered Oct 18 '18 at 13:56

Rameez Khan

1
1

VBA Odd Numbers/Mod

4 Answers4