Passing data from VBE to a range of cells

[lademan]

Hi,

I'm trying to fill a range of cells from within a VBE Function.
I call this function from within the excel sheet with the below codes:
=================================================
Public Function GetByInd(ByRef dataAs Variant) As Variant
Call ApplyDataToCells(data)
GetByInd = ""
End Function
=================================================

The below are the codes used:
========================================================
Public Sub ApplyDataToCells(ByVal sqlData As String)
Dim Res()
Dim i, j As Long
Dim TheRange As Range
Dim sqlData As String
On Error GoTo ApplyDataToCells_Handle_Err
'The next 2 lines connects and retrieves data from the database
Res = GetResultSet(sqlData)
Application.ScreenUpdating = False
Dim rowNum, colNum As Long
rowNum = ActiveCell.Row
colNum = ActiveCell.Column
i = UBound(Res, 1) + rowNum
j = UBound(Res, 2) + colNum
Set TheRange = Range(Cells(rowNum, colNum), Cells(i, j))
'The code breaks at this point
TheRange.Value = Res
Application.ScreenUpdating = True
ApplyDataToCells_Handle_Exit:
Exit Sub
ApplyDataToCells_Handle_Err:
MsgBox Err.Description, vbOKOnly, "Error"
GoTo ApplyDataToCells_Handle_Exit
End Sub
=====================================================
the code breaks whenever I

The code breaks at the point indicated above With the following errors:
"Application defined or object-defined error"
However, If I put the same code in a standalone sub and run it on its own, it works and populates the range of cells.
I can seem to get my head round this issue and I'll appreciate all responses with regards resolving this issue.

cheers,

[Bob Phillips]

You cannot write to cells from within a UDF, you can only return a result to the calling cell.

[lademan]

Cheers for your response.
Whats the the best alternative for returning data to a range of cells from within VBE?

[Bob Phillips]

It depends upon how you want to initiate the action.

If you wanted do it from a button or a menu, just the second proc as you have.

If you want it triggered by an event, such as activating a sheet, changing a cell, then worksheet events are good.