Active content security warning in all files

Question

So I have this code that sets object properties of a class in a for loop, saving each object as an element in an array, BREobjects(). The very next code is below and the first BREobjects(i).BREdays is throwing an

Object variable not set error.

It's a Public array so it shouldn't need to be redim'ed or anything. Anyone know what's happening?

Code that sets the object properties:

'creates a new object for each BRE day/time combination
count = 0
For Each i In BREitems
    BREdaysString = Split(Cells(i.Row, "c").value, ", ")
    For j = LBound(BREdaysString) To UBound(BREdaysString)

        count = count + 1
        ReDim Preserve BREobjects(count)
        Set BREobjects(count) = New BREpptObjects

        BREobjects(count).BREname = Cells(i.Row, "a").value
        BREobjects(count).BREcategory = Cells(i.Row, "b").value
        BREobjects(count).BREstartTime = Cells(i.Row, "d").value
        BREobjects(count).BRElength = Cells(i.Row, "e").value
        BREobjects(count).BREtimeRight = Right(Cells(i.Row, "d").value, 2)

        BREobjects(count).BREdays = BREdaysString(j)

        'Sets the start row number accounting for BREs that start on the half hour
        If BREobjects(count).BREtimeRight = 0 Then
            BREobjects(count).BREstartRow = (Cells(i.Row, "d").value / 100) + 3
            BREobjects(count).BREremainder = 0
        ElseIf BREobjects(count).BREtimeRight <> 0 Then
            BREobjects(count).BREstartRow = ((Cells(i.Row, "d").value - BREobjects(count).BREtimeRight) / 100) + 3
            BREobjects(count).BREremainder = 1
        End If

        'determines the row the BRE ends in
        If BREobjects(count).BRElength - Fix(BREobjects(count).BRElength) = 0 Then
            BREobjects(count).BREendRow = BREobjects(count).BREstartRow + BREobjects(count).BRElength - 1
        ElseIf BREobjects(count).BRElength - Fix(BREobjects(count).BRElength) > 0 Or BREobjects(count).BREremainder = 1 Then
            BREobjects(count).BREendRow = BREobjects(count).BREstartRow + Fix(BREobjects(count).BRElength)
        End If

        If BREobjects(count).BREremainder = 1 And BREobjects(count).BRElength >= 1 Then
            BREobjects(count).BREendRow = BREobjects(count).BREendRow + 1
        End If

        'sets the end time
        If BREobjects(count).BRElength - Fix(BREobjects(count).BRElength) = 0 Then
            BREobjects(count).BREendTime = BREobjects(count).BREstartTime + (100 * BREobjects(count).BRElength)
        ElseIf BREobjects(count).BRElength - Fix(BREobjects(count).BRElength) > 0 Then
            BREtimeRight = Right(BREobjects(count).BRElength, 2)
            BREobjects(count).BREendTime = BREobjects(count).BREstartTime + (100 * Fix(BREobjects(count).BRElength)) + (BREtimeRight * 60)
        End If

        BREobjects(count).BREID = BREobjects(count).BREname & " " & BREobjects(count).BREdays & " " & _
        BREobjects(count).BREstartTime & " " & BREobjects(count).BREendTime & " " & BREobjects(count).BRElength
    Next j
    Erase BREdaysString
Next i

'This loop throws an Object variable or with block variable not set error.
'Thrown on the array in the line BREdays = BREobjects(i).BREdays.  
Back:
For i = LBound(BREobjects) To UBound(BREobjects)
    Dim BREdays As String

    BREdays = BREobjects(i).BREdays

    If FiveDay = True And BREdays = "Saturday" Or BREdays = "Sunday" Then
        Call DeleteElement(i, BREobjects())                         'Deletes the BREppt Object from the BREobjects array
        ReDim Preserve BREobjects(UBound(BREobjects) - 1)           'Shrinks the array by one, removing the last one
        GoTo Back                                                   'Restarts the loop because the UBound has changed
    End If
    Debug.Print BREobjects(i).BREID
Next i

Solution 1

Another way to fix this is to create a Trusted Location for your project. you can use a registry key to make your folder a Trusted Location. This is actually easier then is sounds once you know what key to set.

The registry key is slightly different for each version of MS Access, but here is a sample key for MS Access 2010:

[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Access\Security\Trusted Locations\Location20] "Path"="C:\Database\" "Description"="My Database location"

If you copy and paste the above quote into a text file and save it with a name such as RemoveSecurityWarning.reg, you can then run (merge) the file into your computers registry by simply double clicking the file.

Now lets explain a few things about what this key did:

• The number "14.0" is the version of MS Office. You can simply change that to the number that represents the version you are running.
• The "Location20" is a unique name that we assigned. The 20 can be any number that is not already used. Other programs, including default MS Access wizards, already may have used other numbers. Or if you want to make more then one path as a trusted location, then each location must end with a different number.
• The "C:\Database\" actually is the physical path that you want to set to be a Trusted Location. You can put any path that you choose here. I am not sure why it uses double \, but I just copied the example of how the previous keys were set up and this seemed to work best.
• The Description is not needed, but obviously any description can be used.

For more details, read this article: http://blog.ideaz.net/2013/12/how-to-remove-microsoft-access-security.html

Solution 2

Access 2010 automatically safeguards against all macros unless you tell it otherwise. It also will disable content of files which it doesn't trust. You must tell it which ones to trust via the Trust Center.

In Access 2010, the Trust Center controls what content is allowed and can be accessed by File > Options > Trust Center > Trust Center Settings button.

Within the Trust Center, you can change macro settings and add files and locations to the trust settings.

Comments

[Álvaro González]

To downvoter: I'll be glad to edit the question in order to improve it. But I need to know what's wrong.

[P. O.]

On Access 2007 , go to the Windows thingy on the top left, chose Options, at the bottom of the list you'll find sthg like "Security center" or "Confidentiality center" (my version is not in English), once you click on that there will be a button with parameters, click on it and change the security settings of Macros. That should do the trick.

[Álvaro González]

@JoBedard - Thanks. The Access 2010 GUI is different. I've browsed all the panes and dialogues about security but I'm only given the choice to block or execute macros—I see no info about what precise macros are triggering the warning.

[P. O.]

To at least get the list of macro and modules did you try to Right-click the Navigation Pane’s title bar--> Choose Navigation Options-->Display Options section-->check the Show Hidden Objects option ?

[Álvaro González]

@JoBedard - Ha ha, I had never suspected there was a context menu in a combo. I can now see several tables that start with MSys but not other kind of objects.

[John MacIntyre]

@ÁlvaroG.Vicario It's frustrating how everybody is just telling you how to get rid of the security alert without knowing how to "find the VBA macros that Access is reporting about". Did you ever figure it out?

[Álvaro González]

@JohnMacIntyre Not actually. I finally assumed that Access displays the warning every time, no matter the file contents.

[BigBen]

More code would be helpful - where is the Set actually taking place?

[BigBen]

This modification of BREobjects within the loop and the use of GoTo is a bit of a code smell btw.

[Chris H.]

Edited the OP adding code. code smell...lmao. never heard that. i thought it was a horrible hack too. but hey it worked.

[CDP1802]

First BREpptObjects is added to array with count = 1 so BREobjects(0) is undefined.

[Chris H.]

moving the count = count + 1 to after the Set BREobjects = New BREpptObjects throws an out of bounds error at count = 1 on the .name property.

[CDP1802]

Have you considered using a collection rather than an array ? You will get confused if you use count both for the array index and the Redim. A dim array(1) has 2 elements (assuming the default Option Base of zero). Leave your code as is and avoid BREobjects(0) by starting your delete loop from 1. Better still put the If day = "Saturday" etc condition in the first loop and avoid inserting those days so no need for second loop.

[Chris H.]

I've never used collections before. I'll have to research it. I imagine it could reduce, or at least force a refactor for the rest of the code too. I see your point about BREobjects(0). This code was developed and works using 2010 and now I'm on 2013 and apparently more than a few things are different. I do like the idea of excluding the weekend days in the creation of the objects rather than having to change the array afterwards. Thats a lot of good direction. Thank you!

[Álvaro González]

I think the "Click for further details" I get points to the equivalent dialogue in Access. But all it says is "VBA Macros" (no details).

[Álvaro González]

Yeah but... Does it check if there are actually macros before showing the warning or the warning gets displayed every time you open a file no matter if it has macros or not?

[Álvaro González]

I guess your comment answers one of my questions: if I'm getting the warning, there must be a macro somewhere in the file. For some reason, my copy of Access must be injecting hidden macros to all files. Virus scanners don't report anything so it must be an add-on or something...

[CharlieRB]

If you add the file to the Trust Center, do you still get the warning?

[Álvaro González]

As far as I can tell, all methods to automatically approve or reject macro execution work correctly.

[Álvaro González]

Strictly speaking, this doesn't answer my question. But it makes me want to change my question :) Great tip. (On the Microsoft side, a security mechanism that's based on file location rather than file contents is possibly not the greatest idea.)