Run Excel vba code on background sheet

Question

I have written a vba script for work that performed great on Windows 7/Office 2010 as long as it was opened in a separate Excel window. Running other workbooks at the same time in the same window resulted in errors. This was a bit off a hassle sometimes but not that much that people had a problem with it so I never took the time to take a look at it.

Very recently our systems were upgraded to Windows 8 and Office 2013 which does automatically open separate excel windows but gives the same errors as on the old system opening several workbooks in one window. The error occurs because I am working on another workbook and the active workbook doesn't contain several sheets the VBA workbook does contain.

I can fix this by activating the VBA workbook+worksheet every time the code runs (every 15 seconds) but this forces the VBA workbook to the front. Is there a way to have the code run on the VBA workbook while not forcing it to the foreground all the time?? At this moment I can't run my VBA workbook on the same pc as any other Excel sheet and this is very inconvenient. Any help would be appreciated!

At this moment I changed several things

ThisWorkbook

Private Sub Workbook_Open()

Set WB = ActiveWorkbook
Set WS = WB.ActiveSheet

End Sub

Module MyModule

Public WB As Workbook
Public WS As Worksheet

Module OtherModule

Sub ColumnNamer()

Dim titleRow As Integer
Dim titlerng As Range

titleRow = 5

Set titlerng = Workbooks(WB).Sheets(WS).Range(Cells(titleRow, 1), Cells(titleRow, 50))

colFind = WorksheetFunction.Match("SEARCH_TAG", titlerng, 0)
End Sub

Calling the ColumnNamer Sub results in the following (manually translated( error: Compile Error (A Matrix/Array is expected).

Chiel · Accepted Answer · 2015-04-08 15:50:51Z

0

Are you referring to ActiveWorkbook? When starting the workbook with the VBA code, I create an object that refers to the active workbook and then use this object. This makes sure that I refer to one certain workbook and not just the ActiveWorkbook which can change all the time.

Create a module and insert:

Public WB As Workbook
Public WS As Worksheet

Now add this code to your ThisWorkbook object:

Private Sub Workbook_Open() 
   Set WB = ActiveWorkbook
   Set WS = WB.ActiveSheet
End Sub

And in another module

Sub ColumnNamer()
    Dim titleRow As Integer
    Dim titlerng As Range
    Dim colfind

    On Error GoTo ErrHandler

    titleRow = 3

    Set titlerng = WS.Range(Cells(titleRow, 1), Cells(titleRow, 50))

    colfind = Application.WorksheetFunction.Match("SEARCH_TAG", titlerng, 0)

    Exit Sub

ErrHandler:
    colfind = -1 'value is not found in given range

End Sub

Note that due to the fact that the third parameter is set to zero, not finding the search value (1st parameter) in the range (2nd parameter) will generate an error. So you'll have to catch that one...

edited Apr 8, 2015 at 15:50

answered Apr 6, 2015 at 13:25

Chiel

1199 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taryn Over a year ago

Comments are not for extended discussion; this conversation has been moved to chat.

Collectives™ on Stack Overflow

Run Excel vba code on background sheet

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related