Multiple Instance of Excel

[Tom59593]

Hey guys,

I am working on an application for a monthly sports tournament that I run. There are three workbooks to this application: the Administration workbook, the Bracket workbook, and the Leaderboard workbook. I currently have a dual monitor setup (extended, not clone) with my laptop in front of me and a large TV on the wall for the players to see. In order for the players to see the bracket and the leaderboard, I open excel, then I open the Bracket.xlsm workbook, then the Leaderboard.xlsm workbook, and I drag those to the TV. I then open another instance of excel on my laptop, and open the Administration workbook. This stays on my laptop and the players cannot see it.

Here's what I want it to do:
In less words, the administration workbook controls the other two workbooks. Every time a player reports a win/loss, I enter that into the Administration workbook, and that needs to update both the bracket and the leaderboard, so the players can see it on the TV. So, basically, all the data is in the administration workbook, and the other two serve as displays for that data in a format the players can understand.

Here's my problem:
When I run a macro that is supposed to update the other two workbooks, I get an error: "Runtime Error '9': Subscript out of range." I believe this is because of the seperate instances of Excel. Since Bracket and Leaderboard are in a different instance than Administration, it can't 'see' those other two workbooks. Below is the part of the code that deals with these three workbooks:

Sub FillBracket()
'Define global variables for worksheets used in function
Dim MatchForm As Worksheet
    Set MatchForm = Workbooks("16_DE_Administration.xlsm").Worksheets("Report Match")
Dim PlayerDB As Worksheet
    Set PlayerDB = Workbooks("16_DE_Administration.xlsm").Worksheets("Player Database")
Dim WBracket As Worksheet
    Set WBracket = Workbooks("16_DE_Bracket.xlsm").Worksheets("W Bracket")
Dim LBracket As Worksheet
    Set LBracket = Workbooks("16_DE_Bracket.xlsm").Worksheets("L Bracket")
Dim FBracket As Worksheet
    Set FBracket = Workbooks("16_DE_Bracket.xlsm").Worksheets("Finals")
Dim Leaderboard As Worksheet
    Set Leaderboard = Workbooks("16_DE_Leaderboard.xlsm").Worksheets("Leaderboard")

Line 8 is the one that is highlighted when the debugger opens. I believe this is due to the seperate instances that I mentioned.

Do you guys have any thoughts about how I could remedy this issue? I thought about trying it in the same instance of Excel, but then I can't display anything on a seperate monitor, and I really don't want the administration tools to be visible to the players! The rest of my code appears to be valid, I just need the connection to the other instance of Excel to go through successfully.

THANK YOU FOR YOUR HELP!!!

-Tom-

 

[Legacy 98055]

Define global variables for function.

Hi Tom. Those variables are not global in scope but procedural. You will need to move them out of the procedure into a standard module's general declaration region. They will then maintain state barring an error.

 

[Tom59593]

Hey Tom,

Could you show an example of this? I know that in Visual Basic (not VBA for Excel) you can declare the variables outside of the 'subs' so that they are "global." However, how can I declare a variable in Excel VBA without it being part of a macro? Or can I just declare them at the beginning of a module before I start the 'subs'?

THANKS FOR YOUR HELP!!!

-Tom-

 

[Legacy 98055]

"Or can I just declare them at the beginning of a module before I start the 'subs'?"

For the most part, the rules are the same. Note that in VB, VBA is a required reference and is integrated into the language. In fact, as far as I know, VB is just VBA with a few more goodies.

 

[Tom59593]

Hey Tom,

Thanks for the quick reply!!! I will test out this idea later after class and I'll respond with any findings...

THANKS!!!

-Tom-

 

[Tom59593]

Hey there,

So I attempted to use the public declarations area at the top of a blank module to get some variables that will be used across all macros, functions, etc. I kept getting an error that said: Invalid outside procedure. I think this has to do with my lack of understanding how to program outside of the 'sub' or 'function' areas. Below is my code as it stands right now. Any ideas?

'Public Declaration Area
 
'Define administration workbook for application
Dim Administration As Workbook
    Set Administration = Workbooks("D:\Tournament Director\32 Player DE\32_DE_Administration.xlsm")
 
'Define tournament name, date, and location
Dim TourName As String
Dim TourDate As String
Dim TourLoc As String
    Set TourName = Administration.Worksheets("Administration").Range("D4").Text
    Set TourDate = Administration.Worksheets("Administration").Range("D5").Text
    Set TourLoc = Administration.Worksheets("Administration").Range("D6").Text
 
'Create a new instance of Excel to hold the bracket workbook
Dim xlApp As Application
    Set xlApp = New Application
    xlApp.Visible = True
    xlApp.UserControl = True
Dim Bracket As Workbook
    Set Bracket = xlApp.Workbooks.Open("D:\Tournament Director\32 Player DE\32_DE_Bracket.xlsm")
 
'Define worksheets for application
Public Admin As Worksheet
Public CPanel As Worksheet
Public PlayerDB As Worksheet
Public WBracket As Worksheet
Public LBracket As Worksheet
Public FBracket As Worksheet
Public Leaderboard As Worksheet
    Set Admin = Administration.Worksheets("Administration")
    Set CPanel = Administration.Worksheets("C-Panel")
    Set PlayerDB = Administration.Worksheets("Player Database")
    Set WBracket = Bracket.Worksheets("W Bracket")
    Set LBracket = Bracket.Worksheets("L Bracket")
    Set FBracket = Bracket.Worksheets("F Bracket")
    Set Leaderboard = Bracket.Worksheets("Leaderboard")
 
Sub GenerateBracket()
...
End Sub

I looked around online but couldn't find any examples of how to declare these variables in this declaration area. Any help would be greatly appreciated!!!

THANK YOU!!!

-Tom-

 

[Legacy 98055]

Hi Tom.

This is a declaration:
Dim Administration As Workbook

This is an assignment:
Set Administration = Workbooks("D:\Tournament Director\32 Player DE\32_DE_Administration.xlsm")

Besides contants, you cannot perform assignments outside of a procedure. So declare you variables outside of the procedures and assign values or references inside of procedures. Post all of your code if you get stuck.

Have a good one.