Running on Local Host Only

[FracinDean]

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?

 

[DanteAmor]

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]

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]

Check if the following works for you.

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]

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

 

[FracinDean]

Check if the following works for you.

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?

    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]

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.

 

[DanteAmor]

Glad to hear it works for you. Thanks for the feedback.