Identify matches between sheet columns

batman1 · #1 02-02-2025, 12:19 PM

Quote:

Originally Posted by gmaxey

Batman,
Perhaps you can explain why this code errors:

1. oKey = oDic.Keys(1).Value

oDic.Add varVals(lngIndex), 1
=> Key = varVals(lngIndex) = STRING
Even if oDic.Keys returns key, key is not an object so it cannot have a property, and it certainly cannot have a VALUE property

2. oKey = oDic.Keys(1) still causes an error. Why?

Take a test and change to…
menu Tools -> References -> select "Microsoft Scripting Runtime"

Code:

Subtest2()
Dim oDic As Scripting.Dictionary, oKey
Dim varVals
Dim lngIndex As Long
 varVals = Split("APPLES,Apples,Pears,Pears,PEARS,pears,Blueberries,APPLES", ",")
 Set oDic = New Scripting.Dictionary
 oDic.CompareMode = 1
 For lngIndex = 0 To UBound(varVals)
 If oDic.Exists(varVals(lngIndex)) Then
 oDic.Item(varVals(lngIndex)) = oDic.Item(varVals(lngIndex)) + 1
 Else
 oDic.Add varVals(lngIndex), 1
 EndIf
Next
For Each oKey In oDic.Keys
MsgBox oKey & " count = " & oDic.Item(oKey)
Next oKey
oKey = oDic.Keys(1)
MsgBox oKey & " count = " & oDic.Item(oKey)
lbl_Exit:
Exit Sub
End Sub

Now there is no error. Why?
---------------
A. My explanation:

VBA is a scripting language, interpreted, not compiled.

In my code, thanks to adding a reference and "early binding", the interpreter already "at the beginning" knows that Keys is a method - a function without a parameter that returns an array, so (1) means the first element of this array. In your code, because of "late binding", the interpreter does not know whether Keys is a method or properties. It's probably looking for a Keys function with one parameter, and there isn't one.

--------------------------------
With "late binding" (your code) you have 2 ways:
1.

Code:

Dim arrKey
arrKey = oDic.Keys ' the interpreter looks for method Keys without a parameter, and it will definitely find
oKey = arrKey(1) ' Keys returns an array, so arrKey is an array and arrKey(1) is the array element at index 1

2.

Code:

oKey = oDic.Keys()(1) ' oDic.Keys() means method without a parameter, and returns an array, so (1) is the array element at index 1
MsgBox oKey & " count = " & oDic.Item(oKey)

I don't know if in the "A. My explanation:" I will explain correctly. In any case, with "late binding" (your code) you have 2 methods given above

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to Remove the subtotal columns in a sheet	Marcia	Excel	3	12-01-2023 05:48 AM
Relating cells in a row in one sheet to cells in columns on another sheet.	mbesspiata3	Excel	2	01-06-2017 05:42 AM
Create a New Sheet from Existing Sheet with Specific Columns	malam	Excel Programming	1	10-17-2014 10:01 PM
From an XL sheet ,how to keep the group of columns which match with other XL sheet	Zubairkhan	Excel	2	03-04-2014 10:57 PM
Removing columns within sheet	shabbaranks	Excel	2	09-11-2012 05:03 AM