help with understanding loops and multiplying integers

[sawyer22]

the goal of my assignment is to create a function that multiplies a series of integers, when given a "low integer" and the number of integers. my issue occurs because n is lower than Lo in the example my professor gave and i cant figure out how to output anything other than 1. Am i missing something super basic in my code? I have already tried stepping through it with no luck. the example was 4 and 3.


Function MultSeries(Lo, n)

If Int(Lo) <> Lo Or Int(n) <> n Then
MultSeries = -999
ElseIf n <= 0 Then
MultSeries = -999
Else
tot = 1
For i = Lo To n
tot = tot * i
Next i
MultSeries = tot
End If
End Function

[Paul_Hossler]

Welcome to the forum -- it'd be worthwhile to read the FAQs in my signature

1. I added CODE tags for you to format the macro -- you can use the [#] icon to insert them next time

2. We don't do homework, but we will provide hints





Option Explicit ' always a good idea

Sub drv() ' just an easy way to debug / test

'use F8 to single step

'test 1
MsgBox MultSeries(10, 5)
'test 2
MsgBox MultSeries(5, 10)

End Sub


'I like to always Dim and define my variables
Function MultSeries(Lo As Long, n As Long) As Long

Dim tot As Long, i As Long
If Int(Lo) <> Lo Or Int(n) <> n Then
MultSeries = -999
ElseIf n <= 0 Then
MultSeries = -999
Else
tot = 1
For i = Lo To n ' see what happens on this step
tot = tot * i
Next i

MultSeries = tot
End If
End Function

[sawyer22]

ok so i stepped through it again, and the only thing i can think of is that with the first example it is taking i and increasing it based on the implied step of 1. I see on the second test it only goes through one time, but what im wondering is how i can write my code to both step +1 and step -1. I tried searching microsofts manual for a solution with no luck.

[Paul_Hossler]

In both cases they reach this line



For i = Lo To n ' see what happens on this step


Case 1 is

For I = 10 to 5


Case 2 is

For I = 5 To 10


When single stepping, did you notice that in Case 1 the looping was never executed (since 10 is already greater than 5) and it jumped right to


MultSeries = tot

[sawyer22]

ah ok i think i am starting to get it thank you

[SamT]

VBA uses the WorksheetFunction Object to access Excel's Functions
MIN() and MAX() are Excel Functions
Abs() is a VBA function that returns the Absolute value of a number

I am a lazy typist so I usually declare a short named variable to "hold" the Worksheet Function Object

Dim WsF as Object
'or
Dim WsFunc As Object
Set Wsf = Application.WorksheetFunction
'or
Set WsFunc = Application.WorksheetFunction

x = WsF.Max(3, 2, 6, 10) 'x = 10
y = WsFunc.Min(3, 2, 6, 10) 'y = 2
z = Abs(-5) 'z = 5

That way you can loop from the Min () of Lo and n to the Max() of Lo and n


You can decide which Sign to use

Dim Sign As Long 'Or as Integer, Integers use less memory, but Longs are more versatile

Sign = 1
If n * Lo < 0 Then Sign = -1

Finally multiply the result of the loop by the Sign.