lademan
02-16-2009, 07:48 AM
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,
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,