What is Dictionary in Excel VBA
HTML-код
- Опубликовано: 23 июл 2024
- What is Dictionary in Excel VBA. An introduction to Dictionary object in VBA.
Here's the VBA code used in this video:
Module1
Option Explicit
Sub createDictionary()
Dim mydictionary As Scripting.Dictionary
Set mydictionary = New Scripting.Dictionary
'MsgBox mydictionary.Count
mydictionary.Add "Dave", 25000
mydictionary.Add "Aslam", 30000
mydictionary.Add "Krishna", 32000
'mydictionary.RemoveAll
'salary increased after 1 year
'Assign new salary
mydictionary("Dave") = 30000
mydictionary("Aslam") = 35000
mydictionary("Krishna") = 40000
Dim i As Long, nextBlankRow As Long
nextBlankRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
For i = 0 To mydictionary.Count - 1
'Debug.Print mydictionary.Keys(i), mydictionary.Items(i)
Cells(nextBlankRow, 1) = mydictionary.Keys(i)
Cells(nextBlankRow, 2) = mydictionary.Items(i)
nextBlankRow = nextBlankRow + 1
Next i
End Sub
Module2:
Option Explicit
Sub WriteDictionary(mydictionary As Scripting.Dictionary, shtReport As Worksheet)
shtReport.Cells.Clear
Dim k As Variant, lRow As Long
lRow = 2
Range("A1") = "Customer ID"
Range("B1") = "Amount"
For Each k In mydictionary.Keys
shtReport.Cells(lRow, 1) = k
shtReport.Cells(lRow, 2) = mydictionary(k)
lRow = lRow + 1
Next
End Sub
Sub GetTotals()
' Create a dictionary
Dim mydictionary As New Scripting.Dictionary
' Get worksheet
Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets("Sheet1")
' Get range
Dim myRange As Range
Set myRange = sht.Range("A1").CurrentRegion
Dim customerID As String
Dim Amount As Long
Dim i As Long
For i = 2 To myRange.Rows.Count
customerID = myRange.Cells(i, 1)
Amount = myRange.Cells(i, 2)
mydictionary(customerID) = mydictionary(customerID) + Amount
Next i
' Get the report worksheet
Dim shtReport As Worksheet
Set shtReport = ThisWorkbook.Worksheets("customerReport")
' Write customer totals
WriteDictionary mydictionary, shtReport
' Clean memory
Set mydictionary = Nothing
End Sub
More details available at www.exceltrainingvideos.com/w...
Useful video
Glad you liked it! Please share the Excel VBA tutorial with your friends too.
Marvelous Dinesh. Hat tip to you.
Glad you found the video useful. Thanks for the feedback.
Buen vídeo como siempre
Thanks for the feedback!
dear mr. takyar, i have been following your chanel for some time and
have lots of inspiration from this. at the moment i am stuck wirth a
problem! i need to sort 2 columns which are dependant on other. for
example column a, cell a1 contains the english word "agile" , column b,
cell b1 contains the german translation "agil". there are many many rows
(over 500) each with unsorted words in column a und the translation in
column b. the column a needs to be sorted, complete with the column b,
which contains the transation, automatically. could this be be possible
with just a formular? the use of a macro or other vba code?
kind regards,
colin huntley
Will work on this problem.
This is a great video! It makes understanding dictionary basics very easy. If I have a table in another workbook or worksheet that has a column of keys and a column of items, is there a way to look at those columns and add all the items to the dictionary dynamically?
You can try using the 'path' to the values.
Thank you! I will give that a try.
sir i am creat a userfrom in vba . i want when i enter greater than vallue a specic cell than msg show "insaffisant balance " how to possible this ??
I replied to your query earlier.
sir i creat a worksheet like saving account . money withdrawals and deposit . and i also creat a user from to manage this worksheet. i want when i enter withdrawals amount greater than available balance. than user from show error massage not avilable balance when i submit this through user from . plz help me sir what code i use for this. thanks in advance
We assume the balance is in column 3:
Private Sub CommandButton1_Click()
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, 3).End(xlUp).Row
If Val(TextBox1.Value) > Sheet1.Cells(lastrow, 3) Then
MsgBox "Available balance is less than withdrawal amount!"
End If
End Sub
I created macro for to open different work book and it executed but my workbook opened including my original file location, ex: i saved as Book1 it shows in workbook top name as book1-c:/my path also, how can i remove that, kindly suggest.
Can you share your code here?
@@Exceltrainingvideos
Hello sir, here is my code..
Sub input ( )
Dim fso as object
Set fso = vba. Createobject("scripting. Filesystemobject")
i = GetFolder ()
Workbooks("Mymacro. xlsm").sheet("main").Range(a1).value = i
Application.caption = Thisworkbook. Path
Source = Thisworkbook. Path & "\support\input.xlsx"
Destination = workbooks ("Mymacro. xlsm").sheets("main").range("A1").value & "\input. Xlsx"
End sub
The purpose of the macro is.
When i click the macro it will ask for the destination and if the destination selected it will give one input excel which is already available in support folder there people need to update their information.
For that only the excel sheet shows file destination with the file name.
PLZ MAKE A VIDEO ON HOW TO INSERT PICTURE FROM USERFORM TO CELL NEXT STUDENT NAME.
PLZZZZZ
PLZZZZZZZZZZ
These links will guide:
www.exceltrainingvideos.com/place-picture-in-excel-worksheet-cell-using-vba/
www.exceltrainingvideos.com/insert-picture-in-multiple-worksheets-automatically/
Do we not need to add Scripting Dictionary from Reference?
In the early binding mode - yes. In the late binding mode you don't nrrd tp do that. You can create the 'object' at runtime.
@@Exceltrainingvideos Thanks al ot Mr Katyar.
sir please beginning to advanced vba learn video step step uploaded. please sir
You can start here: www.exceltrainingvideos.com/excel-vba/excel-2003-vba/
dear mr. takyar, i have been following your chanel for some time and
have lots of inspiration from this. at the moment i am stuck wirth a
problem! i need to sort 2 columns which are dependant on other. for
example column a, cell a1 contains the english word "agile" , column b,
cell b1 contains the german translation "agil". there are many many rows
(over 500) each with unsorted words in column a und the translation in
column b. the column a needs to be sorted, complete with the column b,
which contains the transation, automatically. could this be be possible
with just a formular? the use of a macro or other vba code?
kind regards,
colin huntley
Will work on this problem.