Passing variable to function but not seeing result in sub

XCitableBill

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello.
I'm passing a variable from a sub to a function. The variable is passed into the function correctly and the function pulls the correct data based on the passed in variable. However, I need to use the function result in the sub that called the function and fur whatever reason, I can't get the designated variable to populate with the returned data.

VBA Code:
Sub MultiColSort()
    Dim Sh As Worksheet
    Dim r As Long
    Dim v As Long
    Dim LR As Long
    Dim minval As Long
    Set Sh = Worksheets("Sheet1")
    r = 1
    v = 0
    With Sh
        Do
            r = r + 1
            v = v + 1
            LR = WorksheetFunction.Max(.Range("A" & .Rows.Count).End(xlUp).Row, .Range("E" & .Rows.Count).End(xlUp).Row, .Range("I" & .Rows.Count).End(xlUp).Row, .Range("M" & .Rows.Count).End(xlUp).Row, .Range("Q" & .Rows.Count).End(xlUp).Row)
            If r > LR Then Exit Sub
           
'         Pass row to FindMinValue function
          [B]minval = FindMinValue1(r, minval) [/B] - This is where I'm having the problem. the return variable is the minimum value in the row being interrogated

End Sub
There's more stuff in the sub but it's not relevant to my question.
VBA Code:
Public Function FindMinValue1(r As Long, minval As Long) As Long

Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long
Dim lastcol As Long
Dim dblMin As Double
'Dim minval As Long
Dim cell As Range
Dim rng As Range

Set ws = Worksheets("Sheet1")
Set rng = Nothing
'Get row count
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row

'Get last column - Assumes first row is header
lastcol = ws.Cells(2, Columns.Count).End(xlToLeft).Column

'For i = 2 To lastrow

i = r

If Not cell Is Nothing Then
    Debug.Print "Nothing"
Else
    Set rng = Range(Cells(i, 1), Cells(i, lastcol))
End If
'Worksheet function MIN returns the smallest value in a range

dblMin = Application.WorksheetFunction.Min(rng)

'Displays smallest value
MsgBox dblMin

minval = dblMin

End Function

Why is the result of the function not being passed back to the sub?

Thank you for your time.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,694
Office Version
  1. 365
Platform
  1. Windows
Are you values whole numbers or decimals?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
Not sure what the function is meant to do, and the use of minval in the function and sub doesn't really make sense but to return a value from a function you need to assign the value to the function.
VBA Code:
FindMinValue1 = dblMin
 

XCitableBill

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

The function is meant to interrogate a row of values, find hte minimum value in that row and pass that value back to MultiColSort.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,694
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case for the function use
VBA Code:
Public Function FindMinValue1(Ws As Worksheet, r As Long) As Long
   Dim rng As Range

   Set rng = Nothing
   Set rng = Intersect(Ws.UsedRange, Ws.Rows(r))

   FindMinValue1 = Application.WorksheetFunction.Min(rng)
End Function
and call it like
VBA Code:
minval = FindMinValue1(sh, r)
 

XCitableBill

New Member
Joined
Apr 13, 2020
Messages
11
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you all for your responses. Fluff - I never would have thought of that. Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,694
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,930
Messages
5,627,681
Members
416,266
Latest member
stevenvanroeden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top