Menu
When my colleague writes a macro with the Selection reference in it, for some reason it changes the word Selection to a lower case s and stops it from working.
Example:
Selection.Copy
changes to
selection.Copy
Could someone advise what is happening please? I would guess its his settings somewhere, as he has not previously written any macros.
922
Example. This example uses the LCase function to return a lowercase version of a string. Dim UpperCase, LowerCase Uppercase = "Hello World 1234" ' String to convert. Lowercase = Lcase(UpperCase) ' Returns "hello world 1234".
Step 1: Start the subprocedure by creating the macro. Step 2: Declare the variable as VBA String. Step 3: Assign the value to the variable “k” by applying the “UCASE” function. Step 4: Here, a string is our targeted text value that we are trying to convert to uppercase, and the string value is “excel VBA.”
In VBA we can select any range of cells or a group of cells and perform different set of operations on them, selection is a range object so we use range method to select the cells as it identifies the cells and the code to select the cells is “Select” command, the syntax to use for selection is range(A1:B2). select.
The Substitute function takes a string as the input and replaces a specific text within the string with another text. For example, Substitute(“[email protected]“, “gmail”, “outlook”) will return “[email protected]“. The string within which a specific text will be replaced.
The VB Editor will auto-capitalize according to the casings of the names your code uses for Variables, Sub-Routines and other types, and it will capitalize with roughly the same order as the scopes to a type.
You can resolve the problem by finding any variables called selection and changing the casing to Selection. If you can't find any, try temporarily adding a variable called Selection, and all of the in-scope casings will change.
For example, if you define a variable called workshEEts, notice that using ThisWorkbook.Worksheets.Count will use the same casing:
Dim workshEEts As Workbook
Debug.Print ThisWorkbook.workshEEts.Count
But there's also a glitch in the editor, because it cache's the names even after you delete one. For example, if you delete the first line from the example above, the casing of the 2nd line remains unchanged, and adding a new usage of Worksheets results in the cached casing:
Debug.Print ThisWorkbook.workshEEts.Count
ThisWorkbook.workshEEts(1).Select
So, how do you clear the cache? Create a new variable, even if only temporarily, with the casing you require, and then all of the scoped casings will be updated:
Dim Worksheets
Debug.Print ThisWorkbook.Worksheets.Count
ThisWorkbook.Worksheets(1).Select
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
