How do I call a function inside another function in VBA?

Question

I have a function 1 that looks like this:

Function myFirstFunction(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function

I need to call this function in a separate function like this:

Function myOtherFunction(myVar as Variant) As Variant
Dim Some_Column As Range
Some_Column = Range("B4").Address

If Range((myFirstFunction(Some_Column.Column) & 65536)).End(xlUp) <> Range(myFirstFunction(Some_Column.Column) & 1) Then
...some code
End If

It's giving me an error, I would assume because I'm not able to use the first function in the second function. Is there a way to fix this?

Dan Donoghue · Accepted Answer · 2015-05-28T00:39:43.950

Your function is not passing anything back.

You probably want to change Col_Letter to myFirstFunction in the first function.

Also in the second function change 65536 to rows.count.

It will then error here:

Some_Column = Range("B4").Address

Change it to this:

Set Some_Column = Range("B4")

This is because it is defined as a range so you can't pass the address in (which is a string), you pass the object in (which you have to use Set for)

Should be all good after that.

Function myFirstFunction(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
myFirstFunction = vArr(0)
End Function

Function myOtherFunction(myVar As Variant) As Variant
Dim Some_Column As Range
Set Some_Column = Range("B4")

If Range((myFirstFunction(Some_Column.Column) & rows.count)).End(xlUp) <> Range(myFirstFunction(Some_Column.Column) & 1) Then
'...some code
End If
End Function

score 2 · Answer 2 · edited May 23 '17 at 11:57

The problematic part is Some_Column = Range("B4").Address

Dim Some_Column As Range declares the variable as a reference type, which means you need to set it's reference to an object by stating Set Some_Column = ... and preferably release it in the end with Set Some_Column = Nothing.
.Address returns a String and not an object, so your code should be Set Some_Column = Range("B4") to set Some_Column as a Range.

There's an additional mistake in the first function where Col_Letter = vArr(0) should probably be myFirstFunction = vArr(0), or the other way around. This line sets the function's returned value, function_name = ....

You copied the solution from this question but if you change the function name you have to change it the whole way through.

How do I call a function inside another function in VBA?

2 Answers2