View Full Version : Ask for Explanation
idnoidno
05-02-2017, 03:13 AM
Sub ABC()
Dim d, t, i&, arr
Dim k
'k = Timer
arr = ActiveSheet.UsedRange
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
d(arr(i, 1)) = ""
Next
Sheets(2).Activate
Sheets(1).[1:1].Copy Sheets(2).[a1]
[a2].Resize(d.Count, 1) = Application.Transpose(d.keys)
Set d = Nothing
MsgBox Round(Timer - k, 2)
End Sub
Please help me explain this syntax: d(arr(i, 1)) = ""
mdmackillop
05-02-2017, 03:54 AM
Dictionary objects hold a series of Keys and Values. That line is setting the value for each key as listed in your array to an empty string.
Some useful reading here (https://excelmacromastery.com/vba-dictionary/)
idnoidno
05-02-2017, 04:15 AM
I'm sorry, my English is not very good
I would like to ask is that this paragraph
Is what it means, why is the use of blank strings here?
mdmackillop
05-02-2017, 04:33 AM
Dictionary keys are unique. The code will create a unique list by adding them to a dictionary object. The Value is of no importance for this purpose and an empty string is used. You could use any value in this case; d(arr(i, 1)) = "zzz" would work just as well.
idnoidno
05-02-2017, 04:44 AM
Can I ask further, for learning EXCEL VBA, what advice do you have?
mdmackillop
05-02-2017, 04:51 AM
Search VBA for Beginners. There are many resources available. Use the macro recorder (I stll do) and edit the code to suit your purpose.
idnoidno
05-02-2017, 05:02 AM
How to learn the correct syntax of vba?
mdmackillop
05-02-2017, 05:24 AM
Use Option Explicit and Intellisense. Check the tickboxes in Tools/Options
idnoidno
05-02-2017, 06:46 AM
I should not express it very clearly, what i mean is how to use the correct and standard syntax.
Aussiebear
05-03-2017, 12:24 AM
By using Option Explicit at the top of your code, forces the coder to define your variables correctly. Intellisense when used allows excel to prompt the user with a selection of correct syntax.
idnoidno
05-04-2017, 12:02 AM
Can this 「d(arr(i, 1)) = ""」 be replaced in another way? For example 「If d.Exists.(arr(i, 1)) Then........EndIf」
mdmackillop
05-04-2017, 01:27 AM
For what purpose?
The line creates the key when it adds the null value, although it could be shown with a different syntax. I refer you to the reading in Post #2
Is this a school exercise or similar?
idnoidno
05-04-2017, 01:49 AM
Do u mean that d(arr(i, 1)) = "" (or d(arr(i, 1)) = "zzz") is the only way of dictionary for solving duplicated case problem?
mdmackillop
05-04-2017, 03:04 AM
I don't see a problem. Dictionary keys cannot be duplicate; Dictionary values can be.
Without knowing what you're trying to do, we can't assist. I repeat; Is this a school exercise or similar?
Alternative method
you can use Advanced Filter manually.
if vba, please try this.
Option Explicit
Sub test()
Dim r As Range, t As Range
Set r = Sheets(1).UsedRange.Columns(1)
Set t = Sheets(2).Cells(1)
r.AdvancedFilter xlFilterCopy, , t, True
Application.Goto t
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.