PDA

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?

mana
05-04-2017, 03:54 AM
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