text: February 2015 ~ Logically Proven
  By Karthik Byggari 07:52 VBA
  This post demonstrates how to create a log file in excel or access using VBA (Visual Basic for Applications) code.
  Consider a scenario, the application is logging the user details into the log file who are accessing the application.
  Here is application may be excel or access which is located in the network location which is accessed by the multiple users.
  The log data contains the application name, who are accessing the application and the application accessed date.
  In the below example I am considering the excel application which logs the data into the LOG file. The application appends the data to the LOG file every time the application is accessed.
  Achieving this functionality by logging the user details when the workbook is opened.
  So, the workbook open event code looks like this -
  LogInfo ThisWorkbook.Name & " opened by " & _
  Application.UserName & " " & Format(Date, "yyyy-mm-dd hh:mm")
  LogInfo is the sub-routine which saves the application name, user name and the access date of an application.
  LogInfo sub-routine :
  Sub LogInfo(LogMessage As String)
  'set path and name of the log file where you want to save
  'the log file
  Const LogFileName As String = "D:\LogData\LogFile.LOG"
  FileNum = FreeFile ' next file number
  Open LogFileName For Append As #FileNum ' creates the file if it doesn't exist
  Print #FileNum, LogMessage ' write information at the end of the text file
  Close #FileNum ' close the file
  If the log file doesn't exist, the application creates automatically at the run-time. But the path should be valid and exists otherwise the application runs into error. And make sure the application is having the permissions to create and write into the log file.
  This method takes one argument which is the message that you want to write into the log file.
  This method appends the log message every time the application is accessed.
  This way the application logs the user details who are accessing the application. You can add the same functionality in many cases for example when user modifies something in the excel or access database you can trigger the code to save the user details and what modifications did.
  LOG file is very useful to analyse the application. So keep logging your application.
  By Karthik Byggari 07:22 VBA
  This post demonstrates how to hide the password text in a text box control using Excel VBA (Visual Basic for Applications).
  First design the user form as shown in the picture below -
  This form contains two textbox controls - one for Username and other for password, labels and two buttons.
  In the properties window (press F4 in see properties in code window) of password textbox control - change the value of PasswordChar to '*'.
  This will enforce the application to show all the characters in a password textbox control as "****".
  If this property value is empty, the characters are visible to the users.
  If you want to display other than '*' you can change the character that you wish. Here changed to '$'.
  You are restricted to enter only one character in PasswordChar property of a textbox control. Even if you try to enter more than one character, the first character will be taken into account and the remaining characters will be removed automatically from this property box.
  Double-click anywhere on the form to create the form initialize event to set the default values or do you want to perform some tasks before login form open.
  'perform actions before opening the login form
  Double click on the buttons to create the respective events of the buttons.
  Please find the sample codes of button events.
  Sample Ok button code:
  'validate the controls
  If txtUserName.Value = "" Or txtPassword.Value = "" Then
  MsgBox "Please enter valid username and password"
  'close the form
  Unload frmLogin
  Sample Cancel button code: To close the form
  Wildcard pattern matching in C
  By Karthik Byggari 05:47 CnCPP
  This post demonstrates how to match a wildcard pattern against a particular string in C programming.
  To achieve this we are using the function fnmatch which is in-built declared in the header fnmatch.h
  int fnmatch (const char *pattern, const char *string, int flags)
  This function tests whether the string is matching against the given pattern. This function returns zero if the string match with the given pattern else the function returns non-zero value FNM_NOMATCH.
  The both string and pattern are strings.
  The third argument flags is a combination of flag bits that alter the details of matching which means you can change the way of matching the string by passing the flag bits.
  The following are the available flags -
  FNM_FILE_NAME
  This flag treats the '/' character specially, for matching file names. If this flag is set, wildcard constructs in pattern cannot match '/' in string. Thus, the only way to match '/' is with an explicit '/' in pattern.
  FNM_PERIOD
  This flag treats the '.' character specially if it appears at the beginning of string. If this flag is set, wildcard constructs in pattern cannot match '.' as the first character of string.
  FNM_NOESCAPE
  Don't treat the '\' character specially in patterns. Normally, '\' quotes the following character, turning off its special meaning (if any) so that it matches only itself. When quoting is enabled, the pattern '\?' matches only the string '?', because the question mark in the pattern acts like an ordinary character.
  If you use FNM_NOESCAPE, then '\' is an ordinary character.
  FNM_LEADING_DIR
  Ignore a trailing sequence of characters starting with a '/' in string; that is to say, test whether string starts with a directory name that pattern matches.
  If this flag is set, either 'foo*' or 'foobar' as a pattern would match the string 'foobar/frobozz'.
  FNM_CASEFOLD
  Ignore case sensitive in comparing string to pattern.
  FNM_EXTMATCH
  Recognize beside the normal patterns also the extended patterns introduced in ksh. The patterns are written in the form explained in the following table where pattern-list is a | separated list of patterns.
  matches any single character in the pattern-list
  matches everything in the pattern-list
  matches one or more occurrences of any of the patterns in the pattern-list
  matches exactly one occurrence of the patterns in the pattern-list
  matches if input string is not matches the patter-list
  [seq](pattern-list)
  matches the given sequence of the patterns in the pattern-list
  [!seq](pattern-list)
  matches if input string is not matches the given sequence of the patterns in the pattern-list
  Ref: GNU C Library
  Different modes of rounding float values in C and C++
  By Karthik Byggari 03:25 CnCPP
  This post demonstrates different modes of rounding float values in C/C++ programming.
  Floating-point calculations are carried out internally with extra precision, and then rounded to fit into the destination type.
  ROUNDING MODES:
  IEEE 754 defines the following four possible rounding modes:
  1. Round to nearest (FE_TONEAREST)
  2. Round toward plus infinity (FE_UPWARD)
  3. Round toward minus infinity (FE_DOWNWARD)
  4. Round toward zero (FE_TOWARDZERO)
  You can decide the following destination types when rounding the float values -
  These functions are available in the header #include <math.h>
  So don't forget to include this header file while working with rounding the float values.
  rint() - this function returns the integral value depending on rounding mode.
  //x is the float value
  double rint(double x)
  If x is +/- infinity, rint() returns x.
  If x is NaN, NaN is returned.
  rintf() - this function returns the single-precision value depending on rounding mode.
  float rint(float x)
  All these rounding modes are defined in the header file #include <fenv.h> as shown below -
  /* FPU control word rounding flags */
  #define FE_TONEAREST 0x0000   //decimal value: 0
  #define FE_DOWNWARD 0x0400   //decimal value: 1024
  #define FE_UPWARD 0x0800   //decimal value: 2048
  #define FE_TOWARDZERO 0x0c00   //decimal value: 3072
  The macro values may change depending on compiler.
  The decimal representation of hex values are useful while setting the rounding mode. This is explained in the later section.
  Now we look into each rounding mode.
  Round to nearest (FE_TONEAREST):
  This is the default mode. In this mode results are rounded to the nearest representable value. If the result is midway between two representable values, the even representable is chosen. Even here means the lowest-order bit is zero. This rounding mode prevents statistical bias and guarantees numeric stability.
  Round toward plus infinity (FE_UPWARD):
  All results are rounded to the smallest representable value which is greater than the result. In other words the values are rounded towards +infinity.
  Round toward minus infinity (FE_DOWNWARD):
  All results are rounded to the largest representable value which is less than the result. In other words the values are rounded towards -infinity.
  Round toward zero (FE_TOWARDZERO):
  All results are rounded to the largest representable value which is less than the result. In other words, if the result is negative it is rounded up; if it is positive, it is rounded down.
  Important note - If the result is too small to be represented, it is rounded to zero. However, the sign of the result is preserved.
  E.g. FE_TOWARDZERO(-0.22) returns -0.
  This is because the negative zero can also result from some operations on infinity, such as 4/-infinity.
  Get and Set Rounding Mode:
  int fegetround(void) - This function is used to to return the currently selected rounding mode.
  int fesetround(int round) -
  This function is used to set the one of the rounding modes. The argument 'round' to this function is either an decimal representation or macro name (e.g. FE_UPWARD) of rounding mode. This function returns non-zero value if it fails to set rounding mode else zero if success.
  Note - Avoid changing the rounding mode if possible. It can be expensive operation which leads to run the code slower than expected. For more details see your compiler documentation.
  fValue = 10.22;
  //by default FE_TONEAREST
  printf("\nFE_TONEAREST:%f\n",rintf(fValue));
  //set to FE_UPWARD, returns zero if success
  //set using macro name
  printf("Set to FE_UPWARD: %d\n",fesetround(FE_UPWARD));
  printf("Currently the rounding mode is :%d\n",fegetround());
  printf("FE_TOUPWARD(10.22):%f\n",rintf(fValue));
  //set to FE_DOWNWARD, returns zero if success
  //FE_DOWNWARD integral representation is 1024
  //set using integral representation of macro name
  printf("Set to FE_DOWNWARD: %d\n",fesetround(1024));
  printf("FE_DOWNUPWARD(10.22):%f\n",rintf(fValue));
  //set to FE_TOWARDZERO, returns zero if success
  printf("Set to FE_TOWARD: %d\n",fesetround(FE_TOWARDZERO));
  printf("FE_TOWARDZERO(-0.22):%f\n",rintf(-0.22));
  printf("FE_TOWARDZERO(1.22):%f\n",rintf(1.52));
  FE_TONEAREST:10.000000
  Set to FE_UPWARD: 0
  Currently the rounding mode is :2048
  FE_TOUPWARD(10.22):11.000000
  Set to FE_DOWNWARD: 0
  Currently the rounding mode is :1024
  FE_DOWNUPWARD(10.22):10.000000
  Set to FE_TOWARD: 0
  Currently the rounding mode is :3072
  FE_TOWARDZERO(-0.22):-0.000000
  FE_TOWARDZERO(1.22):1.000000
  C++ Example: Rounding is guaranteed only if #pragma STDC FENV_ACCESS ON is set.
  Include header files fenv.h and math.h if you are using older versions of C++. C++11 version and later supports cfenv.h and cmath.h header files.
  std::fesetround(FE_DOWNWARD);
  std::cout << "rounding down: \n"
  << "  rintf(2.8) = " << std::rint(2.8) << "\n\n";
  rounding down:
  rint(2.8) = 2
  By Karthik Byggari 10:51 CnCPP
  This post demonstrates how to make your program wait for a period of time.
  It is required to wait out program for a little span of time before executing some statements. For example in case waiting for Input/Output signals.
  The function sleep gives a simple way to make the program wait for a short interval. If the program doesn't use signals, then you can use sleep function to wait reliably the specified interval.
  Otherwise, sleep can return sooner if a signal arrives; if you want to wait for a given interval regardless of signals, use sleep.
  The sleep function is declared in unistd.h
  unsigned int sleep (unsigned int seconds)
  The sleep function waits for seconds or until a signal is delivered, whichever happens first.
  The sleep function returns either requested interval is over, it returns a value zero else because of deliver of a signal, it returns a non-zero (remaining time) in the sleep interval.
  You can call sleep function again if the sleep returns a non zero value as long as signals arrive infrequently. If the signals are arriving in rapid - it is difficult to decide the wait time whether to shorten or lengthen the wait time.
  On some systems, sleep can do strange things if your program uses SIGALRM explicitly.
  On GNU systems, it is safe to use sleep function and SIGALRM in the same program, because sleep doesn't work by means of SIGALRM.
  printf("before sleep() function\n");
  Sleep(10); //10 seconds
  printf("after 10 seconds");
  This program waits for 10 seconds before printing "after 10 seconds".
  Extract file name from a path in Excel using formula and VBA
  By Karthik Byggari 10:10 VBA
  This post demonstrates how to extract a file name from a path using Excel and VBA.
  The following are the ways to extract a file name from a path. You can use any one of these methods to extract a file name.
  1. Using Excel Formula
  2. Using VBA code
  3. Using File System Object (VBA)
  Using Excel Formula:
  =MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))+1,LEN(A1))
  Consider range A1 contains a path "C:\Test\file.txt".
  Output of this formula is "file.txt"
  Using VBA code:
  Sub extractfilename()
  Dim sFullFilename As StringUsin
  sFullPath = Sheet1.Range("A1").Value
  If sFullPath = "" Then
  sFullFilename = Right(sFullPath, Len(sFullPath) - InStrRev(sFullPath, "\"))
  sFilename = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))
  Sheet1.Range("B1").Value = sFilename
  One more way to get the file name is using file system object.
  If you want to use file system object, first we have to add reference to the library "Microsoft Scripting Runtime".
  To add, in code window, Select Tools -> Select References -> Search for the library in the list -> check the button "Microsoft Scripting Runtime" -> click OK.
  Then try the following code to extract the file name from a path. Make sure the path is valid else you will receive an error message. File system objects checks if path exists or not. You can add a condition to check if the files exists or not before extracting using fso.FileExists(path) returns a boolean.
  fileName = fso.GetFileName("C:\Test\file.txt")
  These are some ways to extract file name from a path.
  Update image of an image control dynamically in Excel - VBA
  By Karthik Byggari 09:45 VBA
  This post demonstrates how to update image of an image control in Excel with VBA (Visual Basic for Applications).
  It is required to update the image of an image control in Excel without manually loading the image. This post teaches you how to do this dynamically with the help of VBA code.
  To add an image control in a worksheet:
  In Excel Ribbon select Developer tab -> Click on Insert -> In ActiveX controls -> Select Image Control.
  If you don't see the Developers tab, please check this link which explains how to add-
  http://logicallyproven.blogspot.de/2015/02/worksheet-display-name-vs-code-name-in.html
  You can update the image with the properties of previous image or you can set the new properties of image. The properties include top, bottom, height and width of an image.
  Important note - Please do not debug the code. If you try to debug; application raises an error. This is because we are deleting one of the objects in a worksheet. (error raises at the line where the image control is deleted)
  The following is the code to update the image of an image control:
  Sub UpdateReportImage(strImageName, strImagePath)
  Dim top, length, height, width As Single
  Set shp = Report.Shapes(strImageName)
  'Capture properties of exisitng picture such as location and size
  top = .top
  length = .Left
  height = .height
  width = .width
  Report.Shapes(strImageName).Delete
  Set shp = Report.Shapes.AddPicture(strImagePath, msoFalse, msoTrue, length, top, width, height)
  shp.ScaleHeight Factor:=1, RelativeToOriginalSize:=msoTrue
  shp.ScaleWidth Factor:=1, RelativeToOriginalSize:=msoTrue
  shp.Name = strImageName
  MsgBox "Image Updated", vbOKOnly, "Updated"
  Line 8: Report is the worksheet name and initializing the shp object to the existing image control. If the application fail to find the given image, you will receive an error message.
  From line 10 to 15: Capturing the properties of an existing image. In case if you want to set the new properties of an image, edit the values of top, length, height, width.
  Line 17: After capturing the properties, deleting the shape which is not required.
  Line 19: To add a image control in the worksheet named "Report" with the captured properties with the given name. AddPicture requires following inputs.
  AddPicture(Filename, LinkToFile , SaveWithDocument, Left, Top, width, Height)
  Line 20 & 21: Set the original height and width relative to the new image. Comment these lines if you don't want to re-size the image with respect to new image.
  This way you can change the image control dynamically with the help of VBA.
  Generate PDF document from Excel - VBA
  By Karthik Byggari 09:07 VBA
  This post demonstrates how to generate a PDF document from Excel using VBA (Visual Basic for Applications).
  Sometimes it is required to generate a PDF document from the Excel workbook or worksheet. This post gives you a basic idea how to generate the PDF document from a workbook or a worksheet.
  We have to follow these two points in order to generate a PDF document.
  1. Page set up
  2. Export as PDF
  Page set up:
  It is very important to set up the page of a worksheet before exporting as a PDF document.
  The following line will set the paper size of a worksheet to A4.
  'set worksheet paper size to A4
  'Report is the worksheet name
  Report.PageSetup.PaperSize = xlPaperA4
  If you want to see the available paper sizes please follow this link -
  https://msdn.microsoft.com/en-us/library/office/ff834612.aspx
  If you want to set the paper size of a complete workbook iterate through each worksheet in a workbook.
  Export as PDF:
  The second step is exporting the worksheet/workbook as PDF document. The following example exports the "Report" worksheet to PDF document.
  Sub GeneratePDF()
  Dim ReportNum As Long
  'Set report name
  strFileName = "ReportName"
  'Save the pdf document on desktop
  'set the path if you want to save the pdf document in a different folder
  strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
  'full path
  strFilePath = strPath & strFileName
  'Replace Report with ThisWorkbook if you want to export the complete workbook
  'Export the sheet Report in pdf
  Report.ExportAsFixedFormat _
  Type:=xlTypePDF, _
  Filename:=strFilePath, _
  In line 17: we are getting the user's desktop path. This line works only if have a reference to the "Microsoft Shell Controls and Automation" library. Please add a reference to this library.
  For adding, in code window
  Select Tools -> Select References -> Search for the library in the list -> Check the button "Microsoft Shell Controls and Automation" -> Press OK.
  In line 29: This code exports only one worksheet. If you want to export the entire workbook, replace the Report in line 29 with ThisWorkbook.
  In line 30: We are defining the type of document to export. In our case PDF.
  In line 31: Mention full path. you can give directly the file path including file name in double-quotes (e.g. "C:\ReportName").
  In line 32: Mention the quality of the document. (e.g. images) The other available quality is xlQualityMinimum if you reduce the memory size of the exported document (e.g. if your report has more images).
  In line 35: If you change the value to "True", the PDF document opens immediately after generating.
  This way you can export the workbook/worksheet to PDF.
  Difference between worksheet display names and code names in Excel (VBA)
  By Karthik Byggari 08:17 VBA
  This post demonstrates the difference between worksheet code names and worksheet display name. And also explains which name is better to use in the VBA (Visual Basic for Applications) code.
  In an excel workbook, every worksheet has two names - display name and code name.
  Display name is the name visible to every user and every user is allowed to edit the display name of the worksheet by default (if workbook is not protected).
  Code name is the another name of a worksheet which is not visible to every user. The code name is visible only in the code window of workbook application. Press Alt+F11 to view the code names of worksheets in the code window.
  The another way to open the code window is go to Developer tab in the excel ribbon, click on Visual Basic as shown in the picture below.
  If you don't see the developer tab, follow this (Microsoft Excel 2010)- select excel file menu (office symbol on the top left) -> select Excel Options -> Select Popular Section -> check the button "Show Developer tab in the Ribbon" and press OK as shown in the picture below -
  Once you press the Alt+F11, the following code window opens where you will find the code names of the worksheets.
  To view the properties window press F4 or in the code window select View menu and click on Properties Window.
  Then select any one of the sheets, you get all the properties of the sheet and you can edit the code name of the sheet in the (Name) column. This name is only visible in the code window but not to the every user.
  Press Alt+F11 again to switch between code window and excel workbook.
  Understand why it is important to use code names in your code?
  Consider you are writing VBA code to perform some tasks with the worksheets in a workbook.
  In your code you are referring to a worksheet with a display name as shown below. Consider the code name and display name of worksheet is Sheet1.
  ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "Hello,"
  ThisWorkbook.Sheets("Sheet1").Range("B1").Value = "this"
  ThisWorkbook.Sheets("Sheet1").Range("C1").Value = "is a"
  ThisWorkbook.Sheets("Sheet1").Range("D1").Value = "test code."
  This code works very well until unless user is not changing the display name of a worksheet.
  What happens if user changes the display name of a worksheet. You will get the following error message because the application is unable to find the display name of a worksheet. (considering without error handling for better explanation)
  In this case, it is an extra work to search all your code and replace the display names of the sheets. This is a bad solution to replace because if user changes again the display name. Thus it is never ending process.
  The permanent solution is not to use display names of a worksheet in the code. The optimal solution is to use code names or getting the index of a worksheet by searching the code names as shown below.
  Using code names directly in the code:
  Sheet1.Range("A1").Value = "Hello,"
  Sheet1.Range("B1").Value = "this"
  Sheet1.Range("C1").Value = "is a"
  Sheet1.Range("D1").Value = "test code."
  This code works fine even the user renames the display name of a worksheet. But it is better not to refer the worksheet so many times. Unnecessarily the memory size of application grows. If you are using just one or two times in method/function it is okay. If you want to refer more times initialize an worksheet object as shown below.
  Using index of a worksheet by searching code names:
  'declaring the variable of type worksheet
  Dim wksInput As Worksheet
  'initialize the object
  Set wksInput = ThisWorkbook.Sheets(GetWorksheetIndex("Sheet1"))
  'with keyword can be used as below
  With wksInput
  .Range("A1").Value = "Hello,"
  .Range("B1").Value = "this"
  .Range("C1").Value = "is a"
  .Range("D1").Value = "test code."
  'release the object before exit
  Set wksInput = Nothing
  Here we are getting the index of a worksheet by searching the code name of a worksheet "GetWorksheetIndex". And we are using worksheet object wksInput for speeding the application. Never forget to release the worksheet object if you don't require any more to speed up the application.
  GetWorksheetIndex function:
  Function GetWorksheetIndex(strSheetName As String) As Integer
  Set wkb = ThisWorkbook
  'iterate through all worksheets in a workbook
  For intCounter = 1 To wkb.Worksheets.Count
  If UCase(wkb.Worksheets(intCounter).CodeName) = UCase(strSheetName) Then
  intIndex = intCounter
  GetWorksheetIndex = intIndex
  endthis:
  Set wkb = Nothing
  MsgBox Err.Description, vbOKOnly, "Error in GetWorksheetIndex"
  Resume endthis
  Even user changes the display name of a worksheet, we never run into errors because we are using code name of a worksheet. In case if you want to change the code name of a worksheet, it is required to change the code names in the code else you will run into runtime errors. Please use error handlers for the smooth running of your application as shown in the GetWorksheetIndex function.
  Export Excel Worksheet(s) to a new workbook in Excel - VBA
  By Karthik Byggari 09:13 VBA
  This post demonstrates how to export a particular sheet or all worksheets to a new workbook using excel VBA (Visual Basic for Applications).
  In certain situations, it is required to export a current workbook data to a new workbook or do you want to save a backup of some worksheets of a workbook. In either of these cases, you can use the following VBA code to accomplish this task.
  Sub CreateCopy()
  Dim w As Worksheet, nb As Workbook, s As String
  For Each w In ThisWorkbook.Worksheets
  If w.Name <> "Approvals" And w.Name <> "Calcs" Then
  If nb Is Nothing Then
  w.Copy 'creates new workbook and copies sheet to it
  Set nb = ActiveWorkbook
  w.Copy after:=nb.Sheets(nb.Sheets.Count)
  s = ThisWorkbook.FullName
  s = Left(s, Len(s) - 4) & "2.xls"
  nb.SaveAs s
  nb is the new workbook where we are exporting the worksheets.
  In line 3: We are iterating through all the available worksheets in the currently working workbook.
  In line 4: In case if you don't want to export some sheets, exclude those sheets. In this example I am excluding the sheets "Approvals" and "Calcs". If you want to export all the worksheets, remove this condition.
  In line 5,6: If new workbook is not created, it creates a new workbook and copies worksheet.
  In line 9: Copies the worksheet at the end (after last sheet) in the new workbook. You can modify this line if you want to change the order of the worksheets in the new workbook.
  In line 13: Getting the name of the current workbook including the path. (.FullName fetches the name of the workbook as well as the path of the workbook)
  In line 14: Setting the new workbook name. Consider the currently working workbook name is "ExportData.xlsm", this line sets the new workbook name as "ExportData2.xls".
  In line 15: Saving the workbook.
  Dictionary object in Excel - VBA
  By Karthik Byggari 08:39 VBA
  This post demonstrates dictionary object in Excel VBA (Visual Basic for Applications).
  A dictionary class is a data structure that represents a collection of keys and values pair. The key is unique (identical) in a key-value pair and it can have at most one value in the dictionary, but a value can be associated with many different keys.
  A dictionary class contains following members -
  Add(key, item) - add a new key and item to the dictionary.
  CompareMode - set or get the string comparison method.
  Count ()- get the number of items in the dictionary. it is read-only.
  Exists(Key) -  determine if a given key is in the dictionary.
  Item(Key) - get or set the item for a given key.
  Items() - get an array containing all items in the dictionary.
  Key(Key) - change a key to a different key.
  Keys() - get an array containing all keys in the dictionary.
  Remove(Key) - remove a given key from the dictionary.
  RemoveAll() -  remove all information from the dictionary.
  The dictionary is available in the library Microsoft Scripting Runtime. Add a reference to the "Microsoft Scripting Runtime" library as shown below -
  In the code window (Press Alt+F11 to switch between windows) -> Select Tools -> Select References -> From the list find "Microsoft Scripting Runtime" -> check it and Press "Ok".
  The following example illustrates the dictionary class and the members of dictionary.
  'Mandatory to declare variables explicitly
  'declaring global variable
  Dim dict As Dictionary
  Sub DictionaryExample()
  Dim keyArray, itemArray, element
  'Initilalize a dictionary object
  Set dict = New Dictionary
  'dictionary object
  'set compare mode
  .CompareMode = BinaryCompare
  'Other compare modes
  '.CompareMode = DatabaseCompare
  '.CompareMode = TextCompare
  'add item using named arguments
  .Add Key:="mike", Item:=22
  'keys are case-sensitive
  'Mike and mike are different
  '.Add Key:="Mike", Item:=22 'this line gives compiler error
  'add item without named arguments
  .Add "joe", 33
  'case sensitivity and Exists method
  'does MIKE exist?
  Debug.Print "MIKE exists = " & .Exists("MIKE")
  'change key value
  .Key("mike") = "MIKE"
  'extract keys into variant array
  Debug.Print "Array of Keys"
  keyArray = .Keys
  For Each element In keyArray
  Debug.Print element
  'extract items into variant array
  Debug.Print "Array of Items"
  itemArray = .Items
  For Each element In itemArray
  'Items in dictionary
  Debug.Print dict.Count & " Items in Dictionary"
  'empty the dictionary
  .RemoveAll
  //release the dictionary object
  'MIKE Exists = False
  'MIKE Exists = True
  'Array of Keys
  'Array of Items
  '2 Items in Dictionary
  '0 Items in Dictionary
  There are three comparison modes available. By default the comparison mode is BinaryCompare. The other two are DatabaseCompare and TextCompare.
  To understand the difference between these comparison please follow this link - https://msdn.microsoft.com/en-us/library/8t3khw5f.aspx
  Binary comparison is case-sensitive. So 'Mike' and 'mike' are different in the given example. If you try to add the same key twice, you will encounter the following error message.