Hi
I think Excel VBA taking a
.Value or a
.Value2 in different situations is one of those problems like Excel changing numbers into dates or getting confused in different systems with decimal separators and thousand separators.
You will never get a full solution.
There will always be situations when you have to fiddle / adjust your results to get back to the format you want.
There are a few unsolved oddities in Excel and VBA when it comes to
.Value and
.Value2, such as:
http://www.eileenslounge.com/viewtopic. ... 87#p196639" onclick="window.open(this.href);return false;
In this case you find that if you use a range object,
Rng, as first argument in an Index function to return a range of values as output, then Excel VBA decides to give you
.Value2 values for the case of what it sees as dates in the second argument range object.
If you convert that first argument range object,
Rng, to an Array of values first via _..
Arr() = Rng.Value
_.. and then use that instead in the Index function, then any range of output returned retains the date string format.
In the codes I was using VBA did not get mixed up with other things, such as
Strings,
Emptys, number etc. It was able to recognise and preserve that formats in all situations using the
Rng or
Rng.Value. Just the dates it decided to change to
.Value2 in the use of a range.
Excel can be a Bitch sometimes with things like that. :(
I am not too clued up on your specific problem, Yasser, and I have no experience at all with advanced filters ( or even unadvanced ones ) but just to demo what I am talking about, as anything to do with filtering ranges might mess up in the same way as VBA Excel did with me..
Run this simple code on a test range as shown in the screenshot:
Code: Select all
Sub ExcelsABitch()
Dim Rng As Range
Set Rng = Range("A34:C35")
Dim WotEverIGet As Variant
Let WotEverIGet = Application.Index(Rng, 1, Array(1, 2, 3))
End Sub
RngAsFirstArgument.JPG :
https://imgur.com/WcKn4JM" onclick="window.open(this.href);return false;
RngAsFirstArgument.JPG
Run Code like This To see contants of Array:
(From Excel Alt+F11 to get VB Editor: paste code in Code window)
Put stop at End Sub by clicking in left Margin to get Brown circle.
Run Code. ( click in code and Hit F5 for full run or F8 for step mode )
Highlight WotEverIGet.
Hit Shift+F9 to get Watch Window.
Click on + to reveal contents of Array
PutStopInLeftMargin RunCode Highlight WotEverIGet HitShift+F9.JPG : https://imgur.com/zAVfZhB" onclick="window.open(this.href);return false;
Now the same again with an Array as the first argument:
Code: Select all
Sub ExcelsFunk()
Dim Rng As Range
Set Rng = Range("A34:C35")
Dim WotEverIGet As Variant
Let WotEverIGet = Application.Index(Rng.Value, 1, Array(1, 2, 3))
End Sub
RngDotValueArrayAsFirstArgument.JPG :
https://imgur.com/CSRc1cz" onclick="window.open(this.href);return false;
RngDotValueArrayAsFirstArgument.JPG
The problem I had back then was that for other reasons I wanted to use a range object rather than an array as the first argument. The problem with Excel VBA returning the
.Value2 for the case of a date in the
Rng case was a bit of a Bitch.
The problem only occurred with dates
I don’t remember the exact workaround I had at the time, but I think it usually was a bit different in different situations.
Alan
P.s. If you do this code then you get the same results as the first code, so that is suggesting that VBA takes the
.Value2
Code: Select all
Sub ExcelsAWierdo()
Dim Rng As Range
Set Rng = Range("A34:C35")
Dim WotEverIGet As Variant
Let WotEverIGet = Application.Index(Rng.value2, 1, Array(1, 2, 3))
End Sub
In a simple case of just wanting a single row as output then this was a workaround, (as you can get a returned range in such a case), but that was not of so much use as I was picking a 2 dimensional array for my output
Code: Select all
Sub WorkaroundSingleRowOutput()
Dim Rng As Range
Set Rng = Range("A34:C35")
Dim WotEverIGet As Variant
Let WotEverIGet = Application.Index(Rng, 1, 0).Value
End Sub
' or
Sub IndexReturnsALongRowForMyHipHooArray()
Dim Rng As Range
Set Rng = Range("A34:C35")
Dim RangeIGet As Range
Set RangeIGet = Application.Index(Rng, 1, 0)
Dim myArray() As Variant
Let myArray() = RangeIGet.Value ' myHipHooArray.JPG : https://imgur.com/G4TbRps
End Sub
InterceptionImplicitIntersection.JPG :
https://imgur.com/oX5pjxa" onclick="window.open(this.href);return false;
InterceptionImplicitIntersection.JPG
_.___________-
Edit:
P.s. 2: I expect it might never make any difference in your case, but the date as
.Value2 is actually held in a
Double I think, not a
Long. I guess there are two reasons for that
_
Longs end up converted to
Doubles I think somewhere along the line anyway
_ I think dates and dates and time are all held somewhere as part of the same thing, so you need
Double to get the fraction of the day which is time. You might want to bear that in mind and possibly consider using
CDbl rather than
CLng, just in case it might make any difference.
You may sometimes get a problem that a date is not recognised as a date and then
CDbl or
CLng will not work. I have occasionally had this problem. A workaround to that which has sometimes worked has been to do this
Dim dte As Double
dte = CDbl(CDate(SomethingThatLooksToMeLikeADate))
For example in my second code above,
Sub ExcelsFunk() , you would need to do
dte = CDbl(CDate(WotEverIGet(1)))
( and note another Excel quirk… That Array ,
WotEverIGet() , comes from a spreadsheet range( starts at 1 and not 0 )… but unusually is 1 Dimension in this case.…. )
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
