0
Sub MinSelected(R As Range)

Dim min As Double, tmp As String

min = Application.WorksheetFunction.min(R)
tmp = Application.WorksheetFunction.Text(min, "ddd/mm/dd/yyyy")
Range("F2").Select
ActiveCell = tmp
End Sub

Sub MaxSelectedd(S As Range)

Dim max As Double, tmp As String

max = Application.WorksheetFunction.min(S)
tmp = Application.WorksheetFunction.Text(max, "ddd/mm/dd/yyyy")
Range("G2").Select
ActiveCell = tmp

End Sub

Sub Main()
Dim myRange As Range
Set myRange = Selection

Module5.MinSelected (myRange)
MaxSelectedd (myRange)
End Sub

I'm fairly new to VBA and having some trouble with using Subs.

I'm trying to output min and max of the user selected range.

MinSelected method is in Module 5, and Main and MaxSelectedd are in Module 6.

Object required error occurs when I call Module5.Minselected(myRange).

Please help me figure out why.

GSerg
  • 76,472
  • 17
  • 159
  • 346
hotkoreanchick
  • 49
  • 1
  • 2
  • 9
  • 1
    possible duplicate of [VB6 pass by value and pass by reference](http://stackoverflow.com/questions/10262186/vb6-pass-by-value-and-pass-by-reference) – GSerg Feb 02 '15 at 22:07
  • @GSerg I wouldn't say so, this is not about byRef/byVal but more about private/public. – Marek Stejskal Feb 02 '15 at 22:19
  • 1
    @MarekStejskal It is not about ByRef/ByVal, it is about parentheses. – GSerg Feb 02 '15 at 22:46

2 Answers2

1

The correct syntax is either:

Call MinSelected(myRange)
Call MaxSelectedd(myRange)

or you can use :

MinSelected myRange
MaxSelectedd myRange
Philippe.H
  • 302
  • 4
  • 13
  • That fixed it! Thank you so much. Would I do the same if I was calling a function? – hotkoreanchick Feb 03 '15 at 14:07
  • To assign the return value of a function to a variable, you will need to put arguments in parentheses. Something like that: result = MyFunction(argument) – Philippe.H Feb 03 '15 at 15:05
0

You do not need to put Module5 before the sub, your sub/functions are public by default, meaning they can be called from all modules.

Also, you have a typo in you Max function, you are using a Worksheet min function again.

Marek Stejskal
  • 2,698
  • 1
  • 20
  • 30
  • Thanks for catching that. But what if I have a function called MinSelected in Module 1? Then I'd have to call Module 5 if I want to use the sub under module 5? – hotkoreanchick Feb 03 '15 at 14:04
  • No, you do not need to. Just try it. Only if you had a sub/function with a same name in multiple modules would the code crash (unless you set them to be private, meaning they would be accissible from their own module only). – Marek Stejskal Feb 03 '15 at 14:27