Excel VBA: how to define public objects subject to loaded references

Do you mean something like:

Public last_result as Object (or variant?)

Private sub ttt()
if(something) then
last_result =

end if

end sub

Basically, yes ( though you need a Set statement in the ttt sub)

Thank you. let me check if it works. I am dealing with the @risk libraries, which are a bit tricky....

My recommendation would be:

1. Put all your public variables into one separate module. Any that you are concerned about references for, declare as Object.

2. Move all the @Risk code into its own module if possible. If you never call any code from that module if the reference is missing, you shouldn't have any problems. (If you can totally late bind, then you don't need the reference at all)

rory, thank you. What is the simplest way to check if the reference link is OK? I could add an exception on error, but it is not a good idea. I believe there should be a direct way to check a link referfence

The Reference object has an IsBroken property (you need, ironically, a reference to the VBA extensibility library set to see the Reference object.). You can check that in the workbook's Open event.

rory, i am stacked with a simple thing -how to identify the missing (non-existing reference). I am trying to avoid using VBProject and VBA extensibility library. So far I am playing with the following code.

Obviously, it generates a compile error, which cannot be bypassed by "On Error".

Is there a way of doing this check in run-time?

Public Sub test()
    On Error GoTo err
    Call riskTest
    On Error GoTo 0
    Exit Sub
err:
    MsgBox ("@risk missng")
End Sub
Public Sub riskTest()
        Dim fit As RiskFitDefinition
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

another problem as it seems that declaring potentially-missing public vars as Objects doesn't work as expected;

Original:
Global bestFitType As RiskFitStatistic
......
bestFitType = RiskKolmogorovSmirnovStatistic

============================
New option with Object:
Global bestFitType As Object
......
Set bestFitType =RiskKolmogorovSmirnovStatistic

=============================

It returns "Type mismatch error"

any suggestions?

Thanks

Please, ignore my last post... RiskKolmogorovSmirnovStatistic is a constant; so I have managed to declare it directly

The main question about finding missing references without using the VBA extensibility library still remains

You don't actually need to set the reference to use the References collection:

Sub CheckRefs()
   Dim ref
   For Each ref In ThisWorkbook.VBProject.References
      If ref.isbroken Then
         MsgBox ref.Name & " is broken"
      Else
         MsgBox ref.Name & " is fine"
      End If
   Next ref
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Thank you, but this is what I am trying to avoid, if possible, i.e. not using using VBProject and VBA extensibility library for the security reasons - to keep unchecked  "Trust access to the VBA project object model"

Do we have any options to check references without VBProject?

Thank you!

Nope. (how could you - the references are part of the project)

If the access to the VBProject isn't trusted, then you can't do anything with code.

OK, maybe I can bypass compiler errors in run-time somehow. Is it doable?