If you build an Excel application of any size then you will probably use more than one workbook. To access information in another workbook the standard way is to link. However, having workbooks linked to one another often leads to problems managing the opening and closing of linked workbooks. In this post I offer a 'soft link' which aims to break the hard links which come as a default and let your code take control.
There is such a thing as dependency hell where it required to gather antecedent code or data. A specific instance on Windows is DLL Hell concerning the loading of correct executable libraries. In Excel, we have our own form which I am calling 'Hard Link Hell'.
I call Hard Link Hell the mess that VBA coders can encounter when we build a VBA application of size that spans multiple workbooks. In my opinion, a VBA coder ought to exercise as much control as possible over the opening and closing of workbooks. Have a cell in one workbook link to another raises the spectre of Excel opening linked workbooks when we were not expecting it.
Admittedly, there is some control over the behaviour. So from the Data ribbon if I select Edit Links then I get the following dialog box...
... where we can see in the bottom right corner the Startup Prompt button which if pressed raises the following dialog ...
Nevertheless, I have had Hard Link Hell in the past where I have had the break links and relink to a new workbook. I remember it being a nightmare. So, in this post I give some code called Soft Links which means VBA code can take control of when to open linked workbooks. The code ships two functions to be called from a worksheet, SoftLink(workbookName, sheetName, rangeName) which actually return an Excel.Range object but Excel is clever enough to call the Value property; but this works only for single cell references. So for multiple cells use SoftLinkValue(workbookName, sheetName, rangeName). Be aware that the source cell(s) must be named using a range name. Also in the listing are some test procedures
Note, you will have to write code to open the source workbooks or you will get a #VALUE!, but we wanted to take control and so comes the responsibility to ensure the source workbook is loaded when this function is calculated. Enjoy!
Option Explicit
'* Use this for a source comprising multiple cells
Public Function SoftLinkValue(ByVal sWorkbookName As String, ByVal sSheetName As String, ByVal sRangeName As String)
Dim rng As Excel.Range
Set rng = SoftLink(sWorkbookName, sSheetName, sRangeName)
SoftLinkValue = rng.Value
End Function
'* Use this for a source comprising single cell, also useful in other VBA code
Public Function SoftLink(ByVal sWorkbookName As String, ByVal sSheetName As String, ByVal sRangeName As String) As Excel.Range
Dim wb As Excel.Workbook
Set wb = OernColItem(Application.Workbooks, sWorkbookName)
If Not wb Is Nothing Then
Dim ws As Excel.Worksheet
Set ws = OernColItem(wb.Worksheets, sSheetName)
If Not ws Is Nothing Then
Set SoftLink = OernWorksheetRange(ws, sRangeName)
End If
End If
End Function
Private Function OernWorksheetRange(ByRef ws As Excel.Worksheet, ByVal sRangeName As String) As Excel.Range
On Error Resume Next
Set OernWorksheetRange = ws.Range(sRangeName)
End Function
Private Function OernColItem(ByRef col As Object, ByVal idx As Variant) As Object
On Error Resume Next
Set OernColItem = col.Item(idx)
End Function
'**** TEST ****
Sub TestVBACallingSoftLink_LocalSheet()
Const csSHEET1 As String = "Sheet1"
Dim rng As Excel.Range
Set rng = SoftLink(ThisWorkbook.Name, csSHEET1, "A1")
Debug.Assert Not rng Is Nothing
If Not rng Is Nothing Then
Debug.Assert rng.Address = "$A$1"
Debug.Assert rng.Worksheet.Name = csSHEET1
End If
End Sub
Sub TestVBACallingSoftLink_ExternalWorkbook()
Const csSHEET1 As String = "Sheet1"
'*** test setup: create new workbook, add a name
Dim wbNew As Excel.Workbook
Set wbNew = Application.Workbooks.Add
Const csNAME_FOO As String = "Foo"
Dim ws As Excel.Worksheet
Set ws = wbNew.Worksheets.Item(1)
ws.Names.Add Name:=csNAME_FOO, RefersToR1C1:="=Sheet1!R4C8"
ws.Range(csNAME_FOO).Value2 = 42
'*** end of test setup:
'*** now we can call our function to get a link to an external workbook
Dim rng As Excel.Range
Set rng = SoftLink(wbNew.Name, csSHEET1, csNAME_FOO)
Debug.Assert Not rng Is Nothing
If Not rng Is Nothing Then
Debug.Assert rng.Address = "$H$4"
Debug.Assert rng.Worksheet.Name = csSHEET1
Stop
End If
wbNew.Close False
Stop
End Sub
No comments:
Post a Comment