Running on Local Host Only

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
107
I have a large Excel model that has a lot of nested looping in the macros, and it is a stochastic model, so I am doing 100s to 1000s of trials for each simulation. The simulations will run in an acceptable amount of time (15-30 minutes) when the file is saved on the hard drive of the same computer that is running Excel; however, if the file is saved on another computer, the file either crashes Excel or it runs so slowly as to not be usable due to all the network data traffic (maybe this is called "latency"). If the user has made this mistake before with this model, they usually recognize the issue quickly, and they can just shut down Excel and resolve the issue. The big problem is when new model users have this issue, it can be very frustrating and sometimes take a long time before they realize something is wrong.

What I want to do is test to determine if the model is running on the same host where the file is saved and when it is not, abort the run and give the user a message telling them why the model was shut down. I think I can do this by looking for C:\ in the first three characters of the path for the workbook, which I think will only occur if the user has the file saved on the host that is running the Excel model.

I have two questions: 1) Do you know any reason testing for "C:\" in the file path will not work, and 2) do you know a better way to do this?
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
I have a large Excel model that has a lot of nested looping in the macros
Maybe if you put your macro here, we can review it and see if there is a way to make it faster.
Also for understanding your macro it would be ideal if you provide a data sample to test your macro. Use XL2BB tool for data samples, see my signature.
 

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
107
Dante, I do not doubt that you or others could find ways to make my macros run faster; however, I not asking for that. Let me clarify, “a better way to do this” is referring to blocking the execution when a local host is not being used.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Check if the following works for you.

VBA Code:
Sub Test_1()
  Dim localdrive As Variant
  localdrive = Left(ThisWorkbook.Path, 1)
  If LCase(localdrive) <> LCase("c") Then
    MsgBox "model was shut down"
    Exit Sub
  End If
  '
  '
End Sub
 

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
107

ADVERTISEMENT

Thank you. I will test soon and let you know.
 

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
107
Check if the following works for you.

VBA Code:
Sub Test_1()
  Dim localdrive As Variant
  localdrive = Left(ThisWorkbook.Path, 1)
  If LCase(localdrive) <> LCase("c") Then
    MsgBox "model was shut down"
    Exit Sub
  End If
  '
  '
End Sub

Dante, I found an exception when it doesn’t work, when the file is saved to the user's "OneDrive". I think I need to also set a Variant localDriveFullPath and then search the full path for the occurrence of “Trusted Documents” — file is on the user’s One Drive but the path also starts with “c:\”. I modified your code as follows, but my test for the occurrence of "Trusted Documents" is not working. I checked and the string variable "localDriveFullPath" does contain the string "Trusted Documents". Can you see how to fix this?

VBA Code:
    Dim localDrive As Variant, localDriveFullPath As Variant, searthStr as String
    localDrive = Left(ThisWorkbook.Path, 1)
    localDriveFullPath = ThisWorkbook.Path
    searchStr = "Trusted Documents"
    If (LCase(localDrive) <> LCase("c")) And _
    (Not localDriveFullPath Like "*" & searchStr & "*") Then
        MsgBox "Run to be shut down.  Save on local drive for host and re-run."
'     Add code for shut down steps here.
    Else
        MsgBox "Run will continue"  'will delete this part - not needed
    End If
 

FracinDean

Board Regular
Joined
Jul 11, 2011
Messages
107
I had the logic wrong. I changed the AND to OR and got rid of the NOT, and now it works. Thank you for the help.
 
Solution

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Glad to hear it works for you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,257
Messages
5,577,016
Members
412,761
Latest member
McCrab
Top