1. Vba Activate Excel Window

Jul 07, 2014 Excel Programming / VBA / Macros. Microsoft Scripting Runtime. I just know that when I used that type of code I need to activate.

Post Contents. Likely one of the most under-utilized data structures in, Dictionaries include features not found in Collections. With their ease of coding and readability, many developers rely heavily on this data structure.

Overview This post within the series completes the focus on. Many parts of this tutorial draw a comparison between Dictionaries and Collections. If you are not familiar with Collections, it is strongly recommended that you read our before reading about Dictionaries. Within many other programming languages, Dictionaries play a central role and can often be used as an alternative to and. Dictionaries are not as well known in VBA, as they are very similar to Collections in behavior. They do provide some added features, however, such as the ability to modify values and easily and search for existing keys.

Unlike Collections, however, Dictionaries cannot contain a “collection” of custom objects and must always have an assigned. Sample File The following macro-enabled Excel file contains all the example scripts presented in this post. Macros must be enabled upon opening. Activate Microsoft Scripting Runtime Dictionaries are not part of VBA’s core scripting set (DLL file), so a reference to the Microsoft Scripting Runtime is required. This involves a one-time step within the VBA editor.

Click on (1) Tools (2) References. A will appear showing a list of (3) Available References. Scroll down the list to (4) Microsoft Scripting Runtime and check the checkbox.

This setting will be tied to the specific Excel file only — it will save the setting on the current open file, but you must go through this step for each separate file for which you plan to use Dictionaries. Dictionaries vs. Collections vs. Array A Dictionary is often compared to a Collection in terms of behavior and features. This table also includes a comparison with an Array, which was in the post on Collections.

Task Dictionary Collection Array Adding Values Easy: Add method will automatically add elements to the end of the collection. A numeric or Key value is required. Easy: Same method, but a Key value is not required (a custom Key value can be assigned) Somewhat Difficult: value must be added when final key (size) is known; must be within constraints. Removing Values Easy: Remove method will remove value; must know key value. Easy: Remove method will remove value and re- the collection; must know key or index value.

Difficult: must be assigned an empty value, or manually re-indexed. Resizing Easy: Will resize automatically when new values are added or removed. Easy: Will resize automatically when new values are added or removed. Somewhat Difficult: Must use the ReDim or ReDim Preserve statements. Modifying Values Easy: Item or Key values can be modified by an assignment operator while using the Item or Key methods. Difficult: Must remove and add a new value in the same position.

Easy: Assign a new value to the array key. Printing Individual Values Easy: Use Item method and Key. Easy: Use Item method and Index or Key. Easy: Use key. Printing All Values Easy:.

Easy: For Each loop, or Do loop. Easy: For Each loop, For loop, or Do loop. Searching for Values Easy: Use Exists method and key Somewhat Difficult: Use a For Each loop to test each value.

Somewhat Difficult: Use a For Each loop to test each value. Using a Dictionary This post will use a Dictionary defined as apartmentData, containing rental details on apartments within a building. Declaring (Binding) There are two methods of declaring Dictionaries: early binding and late binding.

Early Binding Early binding permits an a dictionary to be declared in the declaration area of a or /. This is the most common method of declaration and will allow intellisense to work with the Dictionary. The following is a simple example of early binding. Set apartmentDataLateBind = CreateObject ( 'Scripting.Dictionary' ) Late binding does not permit the use of intellisense, making early binding a preferred method. Late binding may be useful if you need to pass an object through a function for some future Dictionary.

Keep in mind that it is possible to pass a Dictionary through a function. Dictionary Keys Unlike a Collections–where Keys are optional–a custom Key is required for each Item (element) within a Dictionary. In this sense, Dictionaries behave more like one-dimensional arrays. Dictionary Methods Many Dictionary methods are similar to its Collection counterpart. A few differences and new methods do exist, which will be discussed below. If a Dictionary is declared with early binding, intellisense will present a list of available methods after the dot operator.

Intellisense activated for dictionaries. Add The Add method will add a new value to a dictionary. The first parameter requires a key value, and the second requires the item (element) value. Add '1A', 'Street facing, 1 bedroom, 1 bath, large living room.

The second (no brackets) approach will be used for the remainder of this post. As stated previously, one benefit of a dictionary is the use of non-string numeric values as keys.

With that, a numeric value without quotes will work. Even double or long values will qualify as keys.

An error will be returned if an attempt is made at creating a element with a key that already exists. This may be a common occurrence if a Dictionary is declared globally. Item The Item (singular) method will call a specific item (element) value by key reference. The following will call the descriptive (item) value.

For Each keyValue In apartmentData. Keys Unlike the Key (singular) method, key values will be returned through the assigned “element” of the For Each loop. This is useful for printing both the Dictionary key and the Dictionary item, as the key can be inserted as the Item method parameter for each iteration.

This will be presented. Exists The Exists method is one of the unique and most useful methods associated with Dictionaries. Unlike Arrays and Collections–where testing if a key or value exists usually involves error handling or iterating through entire data structures–a dictionary includes a method to test if a key exists with a single line of code. Cells ( 1, 1 ).

Value = apartmentData. Count Will print the count in cell A1. CompareMode The CompareMode method specifies a criteria for duplicate keys. Generally, a unique value is a set of characters which are not repeated at any point. In most situations within VBA, uniqueness is sensitive to case — that is value “1A” is considered identical to value “1a”.

However, in a binary sense, upper-case and lower-case characters are considered identical. By default, Dictionaries are set to TextCompare, meaning that the same value in different cases is considered identical. If you encounter a situation where two “identical” words, but with different cases are required as keys in a dictionary, you can set a dictionary to check binary values instead of text values.

' Error: keys '1A' and '1a' are the same when compared by text. CompareMode with Exists Unfortunately a Dictionary set to BinaryCompare will not permit different cases to be used in a test using the Exists method. Regardless of comparison setting, the case will always need to match for a value of True to be returned. Using and Printing Dictionaries Printing Dictionaries generally follows the same principles and methods of Collections and Arrays, with some modest differences.

Explicit Print Each element within the Dictionary can be accessed individually for printing. This is generally an inefficient way to print all items within a dictionary, but it’s useful for printing one or more specific items when the key is know. Sub DictionaryBasicPrint Dim apartmentData As New Scripting.Dictionary Sheet1.Cells.Clear apartmentData.Add '1A', 'Street facing, 1 bedroom, 1 bath, large living room. ApartmentData.Add '1B', 'Garden facing, 2 bedrooms, 1 bath, small living room.

ApartmentData.Add '2A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '2B', 'Garden facing, 3 bedrooms, 1 bath, small living room. ApartmentData.Add '3A', 'Street facing, 1 bedroom, 1 bath, small living room.

ApartmentData.Add '3B', 'Garden facing, 3 bedrooms, 1 bath, small living room. Sheet1.Cells(1, 1).Value = apartmentData.Item('1A') Sheet1.Cells(2, 1).Value = apartmentData.Item('1B') Sheet1.Cells(3, 1).Value = apartmentData.Item('2A') Sheet1.Cells(4, 1).Value = apartmentData.Item('2B') Sheet1.Cells(5, 1).Value = apartmentData.Item('3A') Sheet1.Cells(6, 1).Value = apartmentData.Item('3B') Sheet1.Cells(7, 1).Value = apartmentData.Item('4A') End Sub.

End Sub Line 20 has been highlighted to show another feature unique to Dictionaries. Notice how the Item parameter is requesting the item assigned to key 4A. However, Key 4A was never added to the Dictionary. Unlike a Collection, which would return a “subscript out-of-” error, the Dictionary will simply treat this as an empty value. While this may be a good feature for avoiding errors, it is the opinion of this author that this is in poor form. It hinders the readability of the code, and could cause issues with future modifications. Unfortunately it is not possible to print a Key value through a Dictionary method, but since the key is needed to refer to an item in the first place, it can simply be printed next to it as a literal or through a variable value.

It is not possible to return a key value through an item reference. For Loop Unless Dictionary keys are in a sequential numeric order (no string values), a For Loop is NOT an ideal method of printing values of an entire Dictionary. Given that For Loops will only iterate through numeric values and Dictionary keys often contain string values, elaborate workarounds will be needed for this to work. In the following script, the Modulus Operator is used to determine if variable i is even or odd. Even numbers will then concatenate the letter “B” to the Item parameter (Key), whereas odd numbers will concatenate “A”.

Sub DictionaryForPrint Dim apartmentData As New Scripting.Dictionary Dim i As Integer Dim j As Integer Sheet1.Cells.Clear apartmentData.Add '1A', 'Street facing, 1 bedroom, 1 bath, large living room. ApartmentData.Add '1B', 'Garden facing, 2 bedrooms, 1 bath, small living room. ApartmentData.Add '2A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '2B', 'Garden facing, 3 bedrooms, 1 bath, small living room. ApartmentData.Add '3A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '3B', 'Garden facing, 3 bedrooms, 1 bath, small living room.

J = 1 For i = 1 To apartmentData.Count If i Mod 2 = 0 Then Sheet1.Cells(i, 1).Value = apartmentData.Item(j & 'B') j = j + 1 Else Sheet1.Cells(i, 1).Value = apartmentData.Item(j & 'A') End If Next i End Sub. End Sub While this works, it’s a bit cumbersome and not very versatile.

This specific approach would not work if one floor had three apartments, say, apartment C. Likewise, the additional counter j (counted once every 2 iterations to change floors), and the are inefficient and reduce readability. If a For Loop is required for your needs, you may want to opt for a Collection as opposed to a Dictionary. While Collections can contain custom key values as strings, each element will also have a numeric (invisible) index number. The lack of an index is a weakness of Dictionaries.

For Dictionaries, a For Loop will work nicely if keys are sequential numeric values. Keep in mind that if a Dictionary item is removed at any point, a gap in the numeric sequence will be created. For Each Loop For Each Loops work extremely well with Dictionaries. This presents an instance where both Keys and Items can be easily printed together. Sub DictionaryForEachKeyPrint Dim apartmentData As New Scripting.Dictionary Dim i As Integer Sheet1.Cells.Clear apartmentData.Add '1A', 'Street facing, 1 bedroom, 1 bath, large living room. ApartmentData.Add '1B', 'Garden facing, 2 bedrooms, 1 bath, small living room.

ApartmentData.Add '2A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '2B', 'Garden facing, 3 bedrooms, 1 bath, small living room. ApartmentData.Add '3A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '3B', 'Garden facing, 3 bedrooms, 1 bath, small living room. I = 1 For Each keyValue In apartmentData.Keys Sheet1.Cells(i, 1).Value = keyValue Sheet1.Cells(i, 2).Value = apartmentData.Item(keyValue) i = i + 1 Next keyValue End Sub.

End Sub In this example, another unique feature of Dictionaries is used, where the For Each Loop actually returns each Key value within its element. This is done by using the Keys method within the For Each statement — the Key value can be used directly in the loop. In this example, the For Each element keyValue is printed in column A (line 17), then used within the Item method parameter to pull the Item value and print it in Column B (line 18).

Alternatively, the Items method can be used to return each Item value through the For Each element (similar to Collections and Arrays). This is the most effective method of printing or using every Item value in a Dictionary. Sub DictionaryForEachItemPrint Dim apartmentData As New Scripting.Dictionary Dim i As Integer Sheet1.Cells.Clear apartmentData.Add '1A', 'Street facing, 1 bedroom, 1 bath, large living room. ApartmentData.Add '1B', 'Garden facing, 2 bedrooms, 1 bath, small living room. ApartmentData.Add '2A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '2B', 'Garden facing, 3 bedrooms, 1 bath, small living room. ApartmentData.Add '3A', 'Street facing, 1 bedroom, 1 bath, small living room.

ApartmentData.Add '3B', 'Garden facing, 3 bedrooms, 1 bath, small living room. I = 1 For Each itemValue In apartmentData.Items Sheet1.Cells(i, 1).Value = itemValue i = i + 1 Next itemValue End Sub.

Sub DictionaryApplication Dim apartmentData As New Scripting.Dictionary Dim searchValue As String Dim updateValue As String Dim i As Integer Sheet1.Cells.Clear apartmentData.Add '1A', 'Street facing, 1 bedroom, 1 bath, large living room. ApartmentData.Add '1B', 'Garden facing, 2 bedrooms, 1 bath, small living room. ApartmentData.Add '2A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '2B', 'Garden facing, 3 bedrooms, 1 bath, small living room. ApartmentData.Add '3A', 'Street facing, 1 bedroom, 1 bath, small living room. ApartmentData.Add '3B', 'Garden facing, 3 bedrooms, 1 bath, small living room.

SearchValue = InputBox('Enter an apartment number.' ) If apartmentData.Exists(searchValue) Then ' Search if input value exists. UpdateValue = InputBox('Update the apartment description.' , Default:=apartmentData.Item(searchValue)) ' If input value exists, request an updated value from user. If updateValue = 'DELETE' Then ' If user inputs DELETE, remove apartment from list.

ApartmentData.Remove (searchValue) Else apartmentData.Item(searchValue) = updateValue End If Else ' If apartment search returns False. MsgBox ('Apartment cannot be found. Try again') End If i = 1 For Each keyValue In apartmentData.Keys Sheet1.Cells(i, 1).Value = keyValue Sheet1.Cells(i, 2).Value = apartmentData.Item(keyValue) i = i + 1 Next keyValue End Sub. End Sub This simple application permits the user to modify apartment details or altogether remove an apartment from the list. Upon starting the script, an input form (line 17) will request an apartment number from the user.

If the apartment entered does not exist, a message (line 26) will appear telling the user “Apartment cannot be found. Try again.” If the apartment does exist, a second input box will appear (line 19) telling the user to “Update the apartment description, or type DELETE to remove”. If the user types in “DELETE” (all caps), the Remove method (line 21) will remove the apartment.

Vba Activate Excel Window

If any other details are changed, the Item method will modify the value (line 23). The script then finishes up by printing each Key and Item with a For Each loop (lines 30-34). Other Notes. Need to sort a dictionary? See our post on using.

The Microsoft Scripting Runtime reference will transfer with the spreadsheet if it is sent to another user on a different computer. In some instances, a dictionary will not work on another computer if the Microsoft Scripting Runtime is not installed, but generally it will be installed in most machines running Windows 7 or greater.

Dictionaries behave similar to Collections when an item is removed — all items after the removed item will move up in order. This is unlike arrays, where an item (element) can be converted to a null/blank value, but cannot be completely removed.

Hi All: I have a macro that was given to me by a Board Member but in order to run it you need to reference Microsoft Scripting Runtime. Is there anyway to set this reference AUTOMATICALLY in the code itself? Right now I have the macro in my Personal Module but would like to send it to others without having them go into VBA to set reference to: Microsoft Scripting Runtime.

If there is a line of code that would turn on this feature I can place it at the beginning the code and then remove the reference at the end of the code.I did see this line of code in another Post but I am not certain it is what I am looking for??? Hi PCC: THANKS I just saw your reply. Do I need to shut that down after the Macro is run or will it turn itself off after the spreadsheet is closed? I noticed when I go into VBA TOOLS REFERENCES and turn it on it seems to be only temporary. Sorry if it is a stupid questions but I do not even know what this reference does other then makes the macro I am using run without error.

If I need to turn it off then how would I accomplish that? Lastly my file is in a different location. It is in C: WINDOWS system32 Is there a way of stating: IF C: WINNT system32 scrrun.dll does not exist then check to see if C: WINDOWS system32 exist? I only ask in case this spreadsheet winds up being used in a different location I am uncertain as to what operating system they use. THANKS Again. I am will try out your code shortly Take Care, Mark. If you use the VBE Library (Visual Basic for Applications Extensibility), the user will be required to set permissions for accessing the VB Project.

This is not the default setting so in most cases it will be a required action which is not a good idea to begin with. It is safe to set an early bound reference to the scripting runtime. It is a standard component with Windows 2000 +, Internet Eplorer 5.5 +, and Office XP +.

There is no benefit to late binding because if the library is there, rather you late bind or early bind, it will work. Hi Norie: SORRY for the confusion. In my first post I was just asking if there was a way to automatically reference Microsoft Scripting Runtime. The line of code I posted at first Set fso = CreateObject('Scripting.FileSystemObject') was just a line of code that I found when searching 'Scripting Runtime' on Mr Excel. I was just asking if that little snippet of code was what I needed to turn on the Scripting Runtime. The other codes I posted are the actual codes I am using in my personal macros.

When I run them it open a box to allow me to select a Folder. The code then runs and creates a list of ALL Files (with Hyperlinks) within the folder that I just selected. So beginning in cell A4 the results would be. Cell A4 will contain the File Name (as a Hyperlink) Cell B4 will contain the Folder name the file is in Cell C4 will contain the Complete File Path (as text) Cell D4 will contain the File Type Cell E4 will contain the Date of the File The only way I could get the codes to run without error was to reference Microsoft Scripting Runtime otherwise I got an error message when the CreateDocsList macro started to run. I think it had something to do with the code: Dim FSO As FileSystemObject I will try out your solution (suggestion) as soon as I get a chance.

THANKS Again, Mark.