Passing Excel objects between functions and procedures in Access.

[Darren Bartrup]

Hi all,

I'm trying to convert some code that I've got in Excel to work in Access. The code opens an Excel workbook, finds where the data is (which will always be a single region) and then does some other stuff which I haven't written yet.

At the moment the code is falling over on the last line of the FindRegionsInWorkbook function - which is the function I'm converting.

The last line of the ExamineExcel procedure below passes the workbook reference to the FindRegionsInWorkbook function. The code falls over on the last line of the FindRegionsInWorkbook function when I'm trying to pass the result back to the main procedure: FindRegionsInWorkbook = rRegions.

I've tried swapping variables between Object and Variant data types and using the SET command, but it keeps coming up with an Object variable or With block variable not set error.

Any ideas please?

Public Sub ExamineExcel(XLPath As String, wkrSht As String, sType As String)
    
    Dim dbs As DAO.Database
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim rst As DAO.Recordset
    Dim blnEXCEL As Boolean
    
    Set dbs = CurrentDb()
    blnEXCEL = False
    
    'Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.Application")
          blnEXCEL = True 'Indicates that Excel is a newly opened application.
    End If
    Err.Clear
    On Error GoTo 0
    
    xlx.Visible = True
    
    Set xlw = xlx.Workbooks.Open(XLPath, , True) ' opens in read-only mode
    Set xls = xlw.Worksheets(wkrSht)
    Set xlc = FindRegionsInWorkbook(xlw)
    
End Sub

'//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'//  Procedure : FindRegionsInWorkbook
'//  Author    : Darren Bartrup [Mar '08] & Zack Barresse (MVP), Oregon, USA.
'//  Purpose   : Returns an array of range references for each list appearing in the
'//              specified workbook.
'// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function FindRegionsInWorkbook(wrkBk As Object) As Object
    Dim ws As Object, rRegion As Object, sRegion As String, sCheck As String
    Dim sAddys As String, arrAddys() As String, rRegions() As Object
    Dim iCnt As Long, i As Long, j As Long
    '//Cycle through each worksheet in workbook.
    j = 0
    For Each ws In wrkBk.Worksheets
        sAddys = vbNullString
        sRegion = vbNullString
        On Error Resume Next
        '//Find all ranges of constant & formula values in worksheet.
        sAddys = ws.Cells.SpecialCells(2, 23).Address(0, 0) & "," '2 - xlCellTypeConstants
        sAddys = sAddys & ws.Cells.SpecialCells(-4123, 23).Address(0, 0) '-4123 - xlCellTypeFormulas
        If Right(sAddys, 1) = "," Then sAddys = Left(sAddys, Len(sAddys) - 1)
        On Error GoTo 0
        If sAddys = vbNullString Then GoTo SkipWs
        '//Put each seperate range into an array.
        arrAddys = Split(sAddys, ",")
        For i = LBound(arrAddys) To UBound(arrAddys)
            arrAddys(i) = "'" & ws.name & "'!" & arrAddys(i)
        Next i
        '//Place region that range sits in into sRegion (if not already in there).
        For i = LBound(arrAddys) To UBound(arrAddys)
            If InStr(1, sRegion, ws.Range(arrAddys(i)).CurrentRegion.Address(0, 0)) = 0 Then
                sRegion = sRegion & ws.Range(arrAddys(i)).CurrentRegion.Address(0, 0) & "," '*** no sheet
                sCheck = Right(arrAddys(i), Len(arrAddys(i)) - InStr(1, arrAddys(i), "!"))
                ReDim Preserve rRegions(0 To j)
                Set rRegions(j) = ws.Range(Left(arrAddys(i), InStr(1, arrAddys(i), "!") - 1) & "!" & ws.Range(sCheck).CurrentRegion.Address(0, 0))
                j = j + 1
            End If
        Next i
SkipWs:
    Next ws
    On Error GoTo ErrHandle
    FindRegionsInWorkbook = rRegions
    Exit Function
ErrHandle:
    'things you might want done if no lists were found...
End Function

The original post for the FindRegionsInWorkbook function is here:
http://www.mrexcel.com/forum/excel-questions/309052-find-all-lists-workbook.html

 

[RoryA]

You need

Set FindRegionsInWorkbook = rRegions

in your function

 

[Darren Bartrup]

Hi Rory,

Thanks for the quick reply.

I've tried that and get this error instead:
Object Required and it highlights rRegions. I've declared it as an object though: rRegions() As Object

Could it be something to do with redimming it?
ReDim Preserve rRegions(0 To j)

 

[RoryA]

Sorry - missed that you were using an array of objects:

Public Function FindRegionsInWorkbook(wrkBk As Object) As Object()

 

[Darren Bartrup]

Hi again Rory,

Sorry that still didn't work. I'm now getting a compile error - Object Required on the Set xlc = FindRegionsInWorkbook(xlw) line of the first procedure and it's highlighting FindRegionsInWorkbook.

xlw is declared as an object and I've added the () to the end of the function name.

If I put xlc = FindRegionsInWorkbook(xlw) it runs through, but then it comes up with my original error again. If I then remove the SET from the function: Set FindRegionsInWorkbook = rRegions. I get Can't assign to array on that line.

Think I'm having real problems with my variable declarations.

 

[RoryA]

Your function is returning an array of Objects, not an Object. Declare the xlc variable either as a variant or as an array of objects.

 

[Darren Bartrup]

Thank you Rory.

I declared XLC as a variant, removed the Set commands and had the function return a variant and everything worked perfectly.

Cheers.