Using numeric variable with a fraction in custom autofilter in vba

Question

I've put my question into an image since I wanted to use screenshots :

Answer 1

Hi,

Thank you for the answer(s).

I am located in Belgium and I am using the local way of representing numbers : a comma (,) to separate decimals and a period (.) as digit grouping symbol. I have been using this setting without any problem for very long time.

The data in the column, where the filtering should be applied on, is in the correct format : number with 2 decimal digits. It was the result of a calculation. It is showing with the comma as separation.

The variable that I use WVNLG_Upper has also the correct formatted number (data type double and also the result of a calculation ) like you can see in the Watchlist of my macro during execution. 

I also tried to use 15.53 in stead of 15,53 (I put this value manually there and I did not change any setting on my computer but then the value that I see in my Watchlist during execution of the macro has no decimal separation any more !! ). So this is even worse.

The problem starts when executing the AutoFilter statement : Excel is leaving out the decimal separation like you can see in the printscreen of the Custom AutoFilter screen. 

Like I mentioned before : I stopped execution of the macro, I corrected the value in the Custom AutoFilter screen manually (putting 15,53 there) and the filtering was done correctly.

Answer 2

Don't know if you are familiar with Stephen Bullen's free Chapter on International Issues in Excel.

It was part of his book on xl2002 but even being so old, has a lot of good information in it.

http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

maybe the original poster will find it useful.

--

Regards,

Tom Ogilvy

Answer 3

Hi Tom,

I also think it is related to the regional settings but IMHO MS has made such a mesh of it that it becomes really difficult to understand how to assign value to a variable.

Having an add-in that allows me to switch between 1.5 & 1,5 as decimal notation. As you can see in my US mode, XL recognizes the value in A1:

1.59

Back to my standard notation:

1,59

EDIT: the copy/paste doesn't show it correctly but believe both are right aligned.

Whatever what notation I use, following macro returns always the same:

Sub test()

   Dim x

   x = [A1]

   Debug.Print x * 2

End Sub

Namely: 3,18 (with comma!!! as this is my default installation)

But I cannot assing 1,59 to the variable like x=1,59, I have to use 1.59.

My add-in is really changing the registry but it looks as some settings are bake in, e.g. in the USA mode I have to use , as argument separator in formulas but still has to use the ; in formulas using something like {1;2;3}.

Answer 4

Excel VBA is generaly US centric.  

In your code try using  15.529  rather than 15,529

I am assuming in your regional version of Excel that the comma is the decimal separator.

--

Regards,

Tom Ogilvy

Answer 5

Hi,

I would suggest to format the specific numeric column as number with 2 or 3 decimal digits.