Using a subroutine's output as a variable in a separate subroutine

Question

Basically what I'm trying to do here is use this sub:

Sub SelectFirstBlankCell()
Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
Dim currentRowValue As String

sourceCol = ActiveCell.Column   'Uses ActiveCell.Column as reference now, but needs to fit into each Subroutine to select next available
rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

'for every row, find the first blank cell and select it
For currentRow = 1 To rowCount
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
        Cells(currentRow, sourceCol).Select
        Exit For
    End If
Next
End Sub

To find the next empty cell in a column to input the string from this sub into.

Set selRange = Selection

For i = 0 To ListBox1.ListCount - 1
  If ListBox1.Selected(i) = True Then
     If strApps = "" Then
      strApps = ListBox1.List(i)
      intAppCodeOffset = i
      strAppCodeVal = Worksheets("TestSheet").Range("B31").Offset(i, 0).Value
    Else
      strApps = strApps & ", " & ListBox1.List(i)
      intAppCodeOffset = i
      strAppCodeVal = strAppCodeVal & ", " & Worksheets("TestSheet").Range("B31").Offset(i, 0).Value
    End If
  End If
Next

Set selRange = selRange.Offset(1, 0)

With selRange
  selRange.Value = strAppCodeVal
End With

I've tried replacing selRage.Offset(1, 0) with SelectFirstBlankCell, but I get an object reference error every time. Any help would be greatly appreciated on this as I can't seem to find how to do it on here.

You're trying to set `selRange` with the name of a subroutine, which isn't valid syntax. It looks like what you're trying to do is have `SelectFirstBlankCell` return a value to `selRange`, so in that case you need it to be a `Function()` so that it returns a value. — dwirony, Oct 05 '18 at 18:01
I'd give [How to avoid using Select in Excel VBA - Stack Overflow](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a read, then start over. — Comintern, Oct 05 '18 at 18:01
A parameter `Sub` procedure doesn't have an "output", it *does* something. Look into `Function` procedures for how you can *return* a value from a procedure, and/or how `ByRef` parameters can be used to that effect (although, a `Function` return value is much cleaner & idiomatic than using a `ByRef` parameter for output). — Mathieu Guindon, Oct 05 '18 at 18:25
I don't see _any_ reference to `SelectFirstBlankCell` in your second block of code, so whether its a `Sub` or a `Function` is irrelevant - it's not being called. _NOTE:_ Once you do call it, it's important that it be a `Function` and return a `Range`. — FreeMan, Oct 05 '18 at 19:16

score 0 · Accepted Answer · answered Oct 05 '18 at 21:13

As mentioned in the comments above, try changing Sub to Function, like this:

Function SelectFirstBlankCell(sourceCol as Integer) as Range

(remove old sourceCol dim and assignment)

...
        Set SelectFirstBlankCell = Cells(currentRow, sourceCol)
...
End Function

Then you can do your change:

Set selRange = SelectFirstBlankCell(ActiveCell.Column) 'Or whatever you think should be defined as the sourceCol

Your code should probably not select anything itself, unless that is the end result of your macro. Try to use the code to directly manipulate the cells instead.

Using a subroutine's output as a variable in a separate subroutine

1 Answers1