VBA connected vs disconnected from network?

[TSW632]

Anyone know why some code would work fine when not plugged into my
company’s LAN, but not when I am plugged into the LAN? I mean, other
than Macro Security, is there something else I should be checking? The
Macro Security shows medium in either condition, but the code will not
work when I am on the LAN… Thanks

Troy

 

[Joel]

There a a few things I can think of

1) Not all commands work on a shared workbook
2) Long file/pathnames have problems if the lengths are long than around 128
characters
3) The code may have problems with accessing filenames on the network verses
on a local PC. For example the default directory will be different

Without seeing the code I cannot really help. If the code is stopping on a
particular line of code the post the code and the actual error. Here are
couple of suggestions to try

1) Comment out all the ON Error statements so the code will stop when you
have an actual error so you can debug the code.
2) Change the Stop On Error level in VBA so the code will stop on all Errors

From VBA menu: Tools - Options - General - Stop On All Errors.

1)

 

[TSW632]

Here is the code:

Private Sub Worksheet_Calculate()
Const CURRENCYTYPES As String = ":GBP£:JPY¥:EUR€:USD$:CAD$:MEX$:BRL
$:"
Static pv As Variant
Dim cv As Variant


cv = Me.Range("V35").Value2


'unless the current value (cv) is a 4-char string different than the
cached
'previous value (pv), there's nothing to do, so exit quickly
If cv = pv Or VarType(cv) <> vbString Or Len(CStr(cv)) <> 4 Then
Exit Sub
Else
cv = ":" & cv & ":"
End If


If InStr(1, CURRENCYTYPES, cv, vbTextCompare) > 0 Then
Me.Range("F49:AF49").NumberFormat = Mid$(cv, 5, 1) & "_([$ -2] *
#,##0_);_([$ -2] * (#,##0);_([$ -2] * ""-""_);_(@_)"
pv = Mid$(cv, 5, 1)
End If
End Sub


So I set VBA to stop on all errors, then tried again. No errors. Could
it be the code isn't being run at all?

And while I'm here, anyone know what to change in NumberFormat so a
currency symbol will be displayed with negative figures as well as
positive?

 

[TSW632]

Here is the code:

Private Sub Worksheet_Calculate()
Const CURRENCYTYPES As String = ":GBP£:JPY¥:EUR€:USD$:CAD$:MEX$:BRL
$:"
Static pv As Variant
Dim cv As Variant


cv = Me.Range("V35").Value2


'unless the current value (cv) is a 4-char string different than the
cached
'previous value (pv), there's nothing to do, so exit quickly
If cv = pv Or VarType(cv) <> vbString Or Len(CStr(cv)) <> 4 Then
Exit Sub
Else
cv = ":" & cv & ":"
End If


If InStr(1, CURRENCYTYPES, cv, vbTextCompare) > 0 Then
Me.Range("F49:AF49").NumberFormat = Mid$(cv, 5, 1) & "_([$ -2] *
#,##0_);_([$ -2] * (#,##0);_([$ -2] * ""-""_);_(@_)"
pv = Mid$(cv, 5, 1)
End If
End Sub


So I set VBA to stop on all errors, then tried again. No errors. Could
it be the code isn't being run at all?

And while I'm here, anyone know what to change in NumberFormat so a
currency symbol will be displayed with negative figures as well as
positive?

 

[Joel]

To see if th emacro is running at all the best way is to put a break point on
the first line of the code (SUB .....). click on the line and then press F9.
The line should change color. Then make a change to the worksheet to force
th emacro to run. The macro should stop on the line with the break point.
You can then sttep through the code using F8.

One thiing I noticed with the code is that the variable pv was not defined
bofre you tested it in the IF statement. You need to initialize PV.

Here is the code:

Private Sub Worksheet_Calculate()
Const CURRENCYTYPES As String = ":GBP£:JPY¥:EUR€:USD$:CAD$:MEX$:BRL
$:"
Static pv As Variant
Dim cv As Variant


cv = Me.Range("V35").Value2


'unless the current value (cv) is a 4-char string different than the
cached
'previous value (pv), there's nothing to do, so exit quickly
If cv = pv Or VarType(cv) <> vbString Or Len(CStr(cv)) <> 4 Then
Exit Sub
Else
cv = ":" & cv & ":"
End If


If InStr(1, CURRENCYTYPES, cv, vbTextCompare) > 0 Then
Me.Range("F49:AF49").NumberFormat = Mid$(cv, 5, 1) & "_([$ -2] *
#,##0_);_([$ -2] * (#,##0);_([$ -2] * ""-""_);_(@_)"
pv = Mid$(cv, 5, 1)
End If
End Sub


So I set VBA to stop on all errors, then tried again. No errors. Could
it be the code isn't being run at all?

And while I'm here, anyone know what to change in NumberFormat so a
currency symbol will be displayed with negative figures as well as
positive?

There a a few things I can think of

1) Not all commands work on a shared workbook
2) Long file/pathnames have problems if the lengths are long than around 128
characters
3) The code may have problems with accessing filenames on the network verses
on a local PC. For example the default directory will be different

Without seeing the code I cannot really help. If the code is stopping on a
particular line of code the post the code and the actual error. Here are
couple of suggestions to try

1) Comment out all the ON Error statements so the code will stop when you
have an actual error so you can debug the code.
2) Change the Stop On Error level in VBA so the code will stop on all Errors

From VBA menu: Tools - Options - General - Stop On All Errors.

1)






- Show quoted text -