w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
VBA code crashing Excel if closed early

For non english languages, you could use .FormulaLocal or .FormulaR1C1Local. Developer reference says "Returns or sets the formula for the object, using R1C1-style notation in the language of the user. Read/write Variant".

However, I strongly recommend not using the above, as it will mean it won't work if the macro is run on a different language version. Instead, better practice is to use English in conjunction with .Formula and .FormulaR1C1. This will still open as French in a French version, as Excel automatically displays formulae text in the relevant language.

For example: (I use "FALSE" only as an example - the below is true for formulae too like "=SUM(A1)", and of course, if you really want to set a boolean value then please don't use string "TRUE"!)

ActiveCell.Formula = "FALSE"

Ok - Locale independent - This will be a FALSE boolean value displayed as FALSE in English and displayed as FAUX in French, but in both cases it is a Boolean value

ActiveCell.FormulaLocal = "FAUX"

'Bad - Locale dependent! - This will be a String "FAUX" if the macro is run on an English version, but a boolean FALSE if run on a French version

ActiveCell.Formula = "FAUX"

'Locale independent, but probably not what you want - This will be a String "FAUX" in all languages

You should not hard-code referring to a sheet by something like "Feuil1". This is just a string name, and Excel will not adapt for the User's locale. Instead, when you add a new sheet, immediately assign it to a worksheet variable, then use that.

For example:

'Bad: it might work if the workbook is made on a French version
but it won't on English and vice versa
Worksheets("Sheet1").Activate 'also bad

With Worksheets.Add
.Name = "Results"
End With
'or (for use outside a With block)
Set resultsWs = Worksheets.Add

As for the rest - I am afraid I do not know what your question is. It is probably crashing sometimes because you are using lots of cut/copy - if it is a very large worksheet or with lots of formulae that recalculate each cut/insert this will take a long time. Unless you need intermediate calculations, disable calculation and screen updating at the start and only re-enable at the end (using Application.ScreenUpdating = False, and Application.Calculation = XLManual)

© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.