Certa vez um leitor perguntou-me a respeito do Excel não diferenciar os valores da célula quando este for 0 (zero) ou for uma seqüência vazia (célula vazia).
Algumas perguntas que permeiam este tema são: Existe uma fórmula para diferenciar valor 0 (zero) de seqüência vazia? Como identificar se valor da célula é 0 ou vazio? Enfim, quando escrevemos uma fórmula que faça referência a qualquer célula vazia o resultado retornado será 0. Faça um teste: Digite 0 na célula A1. Em seguida, digite na célula B1 a seguinte fórmula: = A1 Perceba que o resultado da fórmula é 0 (zero). Ou seja, o algoritmo: SE Célula é vazia ENTÃO Escreva “” SENÃO Escreva o conteúdo desta Célula Traduzindo para uma fórmula no Excel, teremos: =SE(ÉCÉL.VAZIA(A1);””;A1) Note que se você digitar qualquer valor em A1, inclusive 0 (zero) irá retornar este valor. Mas, se você apagar o conteúdo de A1, ou seja, a célula fica vazia, será retornado pela fórmula o valor vazio (“”) e não 0 (zero) como disse no início deste artigo. Funcionou! Agora para fazer a comparação de valores como foi sugerido ao iniciar esta matéria, faça o seguinte para comparar A1 e B1: =SE(ÉCÉL.VAZIA(A1);””;A1)=SE(ÉCÉL.VAZIA(B1);””;B1) Espero que esta dica simples possa salvá-los em algum momento de um possível stress. Faça o download do arquivo no final deste artigo e aproveite para Follow @exceldoseujeito
Excel VBA - Empty, ZLS, Null, Nothing, Missing ----------------------------------- Contents: Empty VarType Function Null Nothing Missing ----------------------------------- In excel vba we often refer to an Empty variable, ZLS (zero-length string) or null string or vbNullString, Null value, Missing Argument, or using the Nothing keyword with an object variable. It is important to differentiate and understand these terms and expressions while using them in your vba code. In this section, we will also understand using the VarType Function to determine the subtype of a variable, using the IsEmpty & IsNull Functions to check for Empty & Null values, and using the IsMissing Function to check whether optional arguments have been passed in the procedure or not. Empty When you declare a variable in your code using a Dim statement, you are setting aside sufficient memory for the variable (viz. 2 bytes for a Boolean or Integer variable, 4 bytes for a Long variable, and so on), and that the information being stored in the variable has an allowable range (of True or False for a Boolean variable, a whole number between -32,768 to 32,767 for an Integer variable, a whole number between -2,147,483,648 to 2,147,483,647 for a variable subtype of Long, and so on). You will receive a run-time error if trying to assign a string value to a variable declared as Integer. While declaring a variable if you do not specify its data type, or if you do not declare a variable at all it will default to Variant data type that can hold any type of data (string, date, time, Boolean, or numeric values) & can automatically convert the values that it contains. However, the disadvantage is that this makes Excel reserve more memory than is required (at least 16 bytes), and could also result in mistyping a variable name and not knowing it viz. you might type rowNumbre instead of rowNumber. When you run a macro, all variables are initialized to a default value. The initial default value: for a numeric variable is zero; for a variable length string it is a zero-length or empty string (""); a fixed length string is initialized with the ASCII code 0, or Chr(0); an object variable defaults to Nothing; a Variant variable is initialized to Empty. In numeric context, an Empty variable denotes a zero while in a string context an Empty variable is a zero-length string ("") . A zero-length string ("") is also referred to as a null string. However, it is advised to explicitly specify an initial value for a variable instead of relying on its default initial value. Empty indicates that no beginning value has been assigned to a Variant variable ie. a variable which has not been initialized. An Empty variable is represented as 0 in a numeric context or a zero-length string ("") in a string context. Empty is not the same as Null which indicates that a variable contains no valid data. The Empty keyword indicates an uninitialized variable value. It is used as a Variant subtype. You can assign the Empty keyword to explicitly set a variable to Empty. IsEmpty Function Use the IsEmpty Function to check whether a variable has been initialized. The function returns a Boolean value - returns True for an uninitialized variable or if a variable is explicitly set to Empty, otherwise the function returns False. Syntax: IsEmpty(expression), where expression is a Variant variable which you want to check. See below example(s) where we use this function to check if a variant variable is empty. Empty, Blank, ZLS (zero-length string), null string & vbNullString ZLS means a zero-length string (""), is also referred to as a null string, and has a length of zero (0). For all practical purposes using vbNullString is equivalent to a zero-length string ("") because VBA interprets both in a similar manner, though both are actually not the same - a 'zero length string' actually means creating a string with no characters, whereas vbNullString is a constant used for a null pointer meaning that no string is created and is also more efficient or faster to execute than ZLS. You can use "" or vbNullString alternatively in your code and both behave similarly. Note that there is no Blank keyword in vba, but we can refer to 'blank cells' or "empty cells" in Excel spreadsheet. There are Excel worksheet functions for empty cells: (i) the COUNTA function counts the number of cells that are not empty, and also counts or includes a cell with empty text ("") - also referrred to as empty string or zero length string - which is not counted as an empty cell; and (ii) the ISBLANK function returns True for an empty cell, and does not treat a zero-length string ("") as a blank (empty cell) similarly as in COUNTA. Both the worksheet functions of ISBLANK and COUNTA distinguish between an empty cell and a cell containing a zero-length string (ie. "" as formula result). VarType Function Use the VarType Function to determine the subtype of a variable. Syntax: VarType(variable_name). The function returns an Integer indicating the variable's subtype. The variable_name can be any variable except a user-defined data type (data type defined using the Type statement) variable. Examples of return values are: value 0 (VarType constant - vbEmpty, uninitialized / default), value 1 (VarType constant - vbNull, contains no valid data), value 2 (VarType constant - vbInteger, Integer), value 3 (VarType constant - vbLong, Long Integer), and so on. The VarType constants can be used anywhere in your code in place of the actual values. Example - Empty variable: Sub EmptyVar() 'variable var1 has not been declared, hence it is a Variant data type: 'returns 0, indicating variable subtype Empty: MsgBox VarType(var1) 'returns True, indicating variable subtype Empty: MsgBox IsEmpty(var1) 'returns False - is an Empty variable, not a Null variable - no beginning value has been assigned to a Variant variable: MsgBox IsNull(var1) 'Empty indicates a Variant variable for which you do not explicity specify an initial value, which by default gets initialized in VBA to a value that is represented as both a zero and a zero-length string. 'returns both messages as below: If var1 = 0 Then MsgBox "Empty Variable represented as Zero" End If If var1 = "" Then MsgBox "Empty Variable represented as a Zero-Length (Null) String" End If End Sub Example - Testing for Empty: Sub EmptyCheck() Dim var1 As Variant 'variable not initialized - returns 0, indicating variable subtype Empty: MsgBox VarType(var1) 'returns True, indicating variable subtype Empty: MsgBox IsEmpty(var1) '----------- 'initialize the variable by specifying a string value: var1 = "Hello" 'returns 8, indicating variable subtype String: MsgBox VarType(var1) 'returns False, indicating variable is not Empty: MsgBox IsEmpty(var1) '----------- 'assign Empty keyword to set variable to Empty: var1 = Empty 'returns 0, indicating variable subtype Empty: MsgBox VarType(var1) 'returns True, indicating variable is Empty: MsgBox IsEmpty(var1) '----------- 'returns True for an empty worksheet cell, otherwise False: MsgBox IsEmpty(ActiveCell) End Sub Example - Initialize a Variant variable: Sub VarInitialized() Dim var1 As Variant 'variable has been initialized to a zero-length string (""): var1 = "" 'returns False, indicating variable is NOT Empty: MsgBox IsEmpty(var1) 'returns 8, indicating variable subtype String: MsgBox VarType(var1) 'returns - "Variable value is a Zero-Length String" If var1 = "" Then MsgBox "Variable value is a Zero-Length String" Else MsgBox "Variable value is NOT a Zero-Length String" End If 'returns - "Variable value is NOT Zero" If var1 = 0 Then MsgBox "Variable value is Zero" Else MsgBox "Variable value is NOT Zero" End If End Sub Example - Check a zero-length string: Sub CheckZLS() Dim var1 As Variant 'variable not initialized - returns 0, indicating variable subtype Empty - represented both as Zero (0) and a Zero-Length (Null) String: MsgBox VarType(var1) 'returns "True" for all If statements below: If var1 = "" Then MsgBox "True" End If If var1 = vbNullString Then MsgBox "True" End If If Len(var1) = 0 Then MsgBox "True" End If End Sub Null In VBA, Null keyword is used to indicate that a variable contains no valid data. A value indicating that a variable contains no valid data. Null is the result - (i) if you explicitly assign Null to a variable, or (ii) if you perform any operation between expressions that contain Null. The Null keyword is used as a Variant subtype ie. only a Variant variable can be Null, and and variable of any other subtype will give an error. Null is not the same as a zero-length string (""), and neither is Null the same as Empty, which indicates that a variable has not yet been initialized. If you try to get the value of a Null variable or an expression that is Null, you will get an error of 'Invalid use of Null' (Run-time Error 94). You will need to ensure the variable contains a valid value. Refer Image 1.
IsNull Function The IsNull Function returns a Boolean value - True for an expression that is Null (containing no valid data), or else False for an expression that contains valid data. Syntax: IsNull(expression). The expression argument is a variant that contains a numeric or string value, and is necessary to specify. Example - Integer variable: Sub VarInteger() Dim intVar As Integer 'returns False (intVar is not Null & neither is it Empty) - no beginning value has been assigned to a variable of subtype Integer: MsgBox IsNull(intVar) 'returns 2, indicating variable subtype Integer: MsgBox VarType(intVar) 'returns - "Variable value is Zero" (The initial default value for a numeric variable is zero) If intVar = 0 Then MsgBox "Variable value is Zero" Else MsgBox "Variable value is NOT Zero" End If End Sub Example - Evaluate Empty / Null variable, use IsNull & VarType vba functions: Sub EmptyNullVar() Dim var1 As Variant 'returns False, var1 is not Null but an Empty variable (no beginning value has been assigned to a Variant variable): MsgBox IsNull(var1) 'variable not initialized - returns 0, indicating variable subtype Empty: MsgBox VarType(var1) 'returns the message because var1 is an Empty variable: If var1 = 0 And var1 = vbNullString Then MsgBox "Empty Variable represented both as Zero (0) and a Zero-Length (Null) String" End If '------------------- 'variable is initialized to a zero-length string ("") or vbNullString: var1 = vbNullString 'returns False - var1 is not a Null variable: MsgBox IsNull(var1) 'returns 8, indicating variable subtype String: MsgBox VarType(var1) '------------------- 'explicitly assigning Null to a variable: var1 = Null 'returns True, for a Null variable, containing no valid data: MsgBox IsNull(var1) 'returns 1, indicating variable subtype Null: MsgBox VarType(var1) '------------------- 'explicitly assigning valid data to a variable: var1 = 12 'returns False, for a variable containing valid data: MsgBox IsNull(var1) 'returns 2, indicating variable subtype Integer: MsgBox VarType(var1) '------------------- 'returns False, for an expression containing valid data: MsgBox IsNull("Hello") End Sub Example - Check a Null variable: Sub CheckNull() 'explicitly assigning Null to a variable: var1 = Null 'returns 1, indicating variable subtype Null: MsgBox VarType(var1) 'returns the message, indicating variable subtype Null: If VarType(var1) = vbNull Then MsgBox "Null variable" End If 'an expression containing Null also evaluates to Null: var2 = Null + 2 'returns 1, indicating variable subtype Null: MsgBox VarType(var2) End Sub Example - Check worksheet cell for Empty, ZLS, Null: Sub WorksheetCell_ZLS_Empty_Null() Dim var1 As Variant 'returns True: MsgBox vbNullString = "" 'In the case where ActiveCell is Blank: 'returns True for a Blank cell: MsgBox ActiveCell.Value = "" MsgBox ActiveCell.Value = vbNullString MsgBox ActiveCell.Value = 0 MsgBox IsEmpty(ActiveCell.Value) 'assign Active Cell value to variable: var1 = ActiveCell.Value 'returns True: MsgBox IsEmpty(var1) MsgBox var1 = vbNullString MsgBox var1 = "" MsgBox var1 = 0 'returns False: MsgBox VarType(var1) = vbNull 'returns 0, indicating variable subtype Empty: MsgBox VarType(var1) 'If you enter "" in the Active Cell ie. the active cell contains the value: ="" MsgBox ActiveCell.Value = "" MsgBox ActiveCell.Value = vbNullString 'returns False: MsgBox ActiveCell.Value = 0 MsgBox IsEmpty(ActiveCell.Value) End Sub Nothing Assigning the Nothing keyword to an object variable disassociates the variable from an actual object. Nothing is assigned to an object variable by using the Set statement. You can assign the same actual object to multiple object variables in vba code, and this association uses your system resources and memory. The system resources and memory get released only either after you assign Nothing to all object variables using the Set statement which disassociates these variables from the actual object, or when all object variables go out of scope and get destroyed. It is advisable to explicity set all object variables to Nothing at the end of your procedure or even earlier while running your code when you finish using them, and this will release memory allocated to these variables. Determine if the object variable is initialized - use Is Nothing for objects: To check if an object has been assigned or set, use the Is keyword with Nothing, viz. If object_variable Is Nothing. For objects, you cannot test if an object_variable is equal to something, and using = instead of Is will give an error. Example - Using the Nothing keyword with an object variable: Sub ObjNothing() Dim objVar As Object 'returns True, because you have not yet assigned an actual object to the object variable: MsgBox objVar Is Nothing Set objVar = ActiveSheet 'returns False, because you have assigned an actual object (Sheet) to the object variable: MsgBox objVar Is Nothing Set objVar = Nothing 'returns "Variable not associated with an actual object", because you have disassociated the object variable from an actual object: If objVar Is Nothing Then MsgBox "Variable not associated with an actual object" Else MsgBox "Actual object is assigned to an Object variable" End If End Sub Missing Passing Arguments to Procedures: When an external value is to be used by a procedure to perform an action, it is passed to the procedure by variables. These variables which are passed to a procedure are called arguments. An argument is the value supplied by the calling code to a procedure when it is called. When the set of parentheses, after the procedure name in the Sub or Function declaration statement, is empty, it is a case when the procedure does not receive arguments. However, when arguments are passed to a procedure from other procedures, then these are listed or declared between the parentheses. Optional Arguments: Arguments can be specified as Optional by using the Optional keyword before the argument to its left. When you specify an argument as Optional, all other arguments following that argument to its right must also be specified as Optional. Note that specifying the Optional keyword makes an argument optional otherwise the argument will be required. Check if an argument is Missing, using the IsMissing function: The Optional argument should be (though not necessary) declared as Variant data type to enable use of the IsMissing function which works only when used with variables declared as Variant data type. The IsMissing function is used to determine whether the optional argument was passed in the procedure or not and then you can adjust your code accordingly without returning an error. If the Optional argument is not declared as Variant in which case the IsMissing function will not work, the Optional argument will be assigned the default value for its data type which is 0 for numeric data type variables (viz. Integer, Double, etc) and Nothing (a null reference) for String or Object data type variables. IsMissing function: The IsMissing function is used to check whether optional Variant arguments have been passed in the procedure or not. Syntax: IsMissing(argname). The function returns a Boolean value - True if no value is passed for the optional argument, and False if a value has been passed for the optional argument. If the IsMissing function returns True for an argument, using the missing argument in the code will cause an error, and thus using this function will help in adjusting your code accordingly. Example of using the IsMissing function to check if an argument is Missing: Function FullName(strFirstName As String, Optional strSecondName As Variant) As String 'The IsMissing function is used to determine whether the optional argument was passed in the procedure, and if not, you can adjust your code accordingly without returning an error. If IsMissing(strSecondName) Then FullName = strFirstName Else FullName = strFirstName & " " & strSecondName End If End Function Sub GetName() Dim strGivenName As String strGivenName = InputBox("Enter Given Name") 'specifying only the first argument & omitting the second argument which is optional: MsgBox FullName(strGivenName) End Sub Page 2
Excel VBA String Functions: Excel VBA String Functions for Finding and Replacing Text, with Examples: LEFT, RIGHT, MID, LEN, REPLACE, InStr & InStrRev Functions ------------------------------------------------------- Contents: LEFT Function (Worksheet / VBA) RIGHT Function (Worksheet / VBA) MID Function (Worksheet / VBA) LEN Function (Worksheet / VBA) REPLACE Function (Worksheet) REPLACE Function (VBA) InStr & InStrRev Functions (VBA) ------------------------------------------------------- In Excel vba, a String refers to a sequence of contiguous characters within quotation marks viz. "This is a string expression within quotation marks, in vba." These characters are literally interpreted as characters, in the sense that these represent the characters themselves rather than their numeric values. A String can include letters, numbers, spaces, and punctuation. A string expression can have as its elements - a string of contiguous characters, a function that returns a string, a string variable, a string constant or a string variant. This section does a detailed discussion on using Excel VBA String Functions to manipulate text strings with vba code. Also refer related link: Excel VBA String Functions: SPLIT, JOIN, CONCATENATE. LEFT Function (Worksheet / VBA) The Excel LEFT function can be used both as a worksheet function and a VBA function. The LEFT function returns the specified number of characters in a text string, starting from the first or left-most character. Use this function to extract a sub-string from the left part of a text string. Syntax: LEFT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the LEFT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null. RIGHT Function (Worksheet / VBA) The Excel RIGHT function can be used both as a worksheet function and a VBA function. The RIGHT function returns the specified number of characters in a text string, starting from the last or right-most character. Use this function to extract a sub-string from the right part of a text string. Syntax: RIGHT(text_string, char_numbers). It is necessary to mention the text_string argument which is the text string from which you want to extract the specified number of characters. The char_numbers argument is optional (when using as a worksheet function), which specifies the number of characters to extract from the text string. The char_numbers value should be equal to or greater than zero; if it is greater than the length of the text string, the RIGHT function will return the text string in full; if omitted, it will default to 1. While using as a VBA function, it is necessary to specify both the arguments, and if text_string contains Null, the function also returns Null. MID Function (Worksheet / VBA) The Excel MID function can be used both as a worksheet function and a VBA function. The MID function returns the specified number of characters in a text string, starting from a specified position (ie. starting from a specified character number). Use this function to extract a sub-string from any part of a text string. Syntax: MID(text_string, start_number, char_numbers). The text_string argument is the text string from which you want to extract the specified number of characters. The start_number argument specifies the character number from which to start extracting the sub-string, the first character in a text string being start_number 1 and incrementing towards the right. The char_numbers argument specifies the number of characters to extract from the text string. If start_number is greater than the length of the text string, an empty string (zero length) is returned; if it is less than the length of the text string but together with char_numbers (ie. start_number PLUS char_numbers) it is greater than the length of the text string, the MID function will return the text string in full from the start_number position to the end of the text string. Using MID function as a worksheet function, if a negative value is specified for char_numbers, MID will return the #VALUE! error value; if start_number is less than 1, MID will return the #VALUE! error value. All arguments are necessary to be specified when using as a worksheet function. Using MID function as a VBA function: The char_numbers argument is optional when used as VBA function, and if omitted the function will return the text string in full from the start_number position to the end of the text string. All other arguments are necessary to be specified when using as a vba function. If text_string contains Null, the function also returns Null. LEN Function (Worksheet / VBA) The Excel LEN function can be used both as a worksheet function and a VBA function. The worksheet LEN function returns the number of characters in a text string. Use this function to get the length of a text string. Syntax: LEN(text_string). It is necessary to mention the text_string argument which is the text string whose length you want to get in number of characters. Note that spaces also count as characters. The worksheet LENB function returns the number of bytes used to represent the characters in a text string - counts each character as 1 byte except when a DBCS language [viz. Japanese, Chinese (Simplified), Chinese (Traditional), and Korean] is set as the default language wherein a character is counted as 2 bytes. Syntax: LENB(text_string). While using LEN as a VBA function - Syntax: Len(text_string) or Len(variable_name) - you can use either a text string or a variable name, and the function will return a Long value representing the number of characters contained in the string or the number of bytes required to store a variable. Using the vba Len function for a variable of type variant will treat the variable as a String and return the number of characters contained in it. A text_string or variable containing Null, will also return Null. The vba Len function returns the number of characters in the string where the variable is of subtype String or Variant, and wherein the variable is of subtype numeric the function returns the number of bytes used to store the variable. Example - Using Left, Right, Mid & Len functions in vba code. Sub Left_Right_Mid_Len() Dim str As String, strLeft As String, strRight As String, strMid As String str = "James Bond" strLeft = Left(str, 7) 'returns "James B", which are the first 7 characters (space is counted as a distinct character). MsgBox strLeft strLeft = Left(str, 15) 'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters. MsgBox strLeft strRight = Right(str, 7) 'returns "es Bond", which are the last 7 characters (space is counted as a distinct character). MsgBox strRight strRight = Right(str, 15) 'returns "James Bond", which are all characters in cell A1, because the number 15 specified in the function exceeds the string length of 10 characters. MsgBox strRight strMid = Mid(str, 2, 6) 'Returns "ames B". Starts from the second character ie. "a", and then specifies that 6 characters be returned starting from "a". MsgBox strMid strMid = Mid(str, 2, 15) 'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the specified characters of 15 plus start number 2 (ie. total of 17) exceed the string length of 10 characters. MsgBox strMid strMid = Mid(str, 2) 'Returns "ames Bond". Returns all characters starting from the second character (start_number position) of "a", because the second argument (char_numbers) is omitted. MsgBox strMid strMid = Mid(str, 12, 2) 'Returns an empty string (zero length), because the start number of 12 exceeds the string length of 10 characters. MsgBox strMid 'Returns 10, the string length measured by its number of characters. MsgBox Len(str) 'Returns 10, the string length measured by its number of characters. MsgBox Len("James Bond") End Sub Example - Using the vba Len function - variable types. Sub Len_vbaFunc()
'returns 3 in both cases - number of characters in the string: MsgBox Len("bad") MsgBox Len("245") 'returns 10 - number of characters in the string including the space: MsgBox Len("James Bond") '--------------------------- 'a variable of type variant is treated as a string Dim vVar As Variant vVar = 245 'returns 2, indicating variable subtype Integer: MsgBox VarType(vVar) 'Returns 3, the number of characters contained in the variable - the Len functions treats the variant variable as a String: MsgBox Len(vVar) '--------------------------- 'a variable of type string Dim strVar As String strVar = "James Bond" 'returns 8, indicating variable subtype String: MsgBox VarType(strVar) 'Returns 10, the number of characters contained in the variable of type String: MsgBox Len(strVar) '--------------------------- 'a variable of type integer Dim iVar As Integer iVar = 245 'Returns 2, the number of bytes used to store the variable: MsgBox Len(iVar) 'a variable of type long Dim lVar As Long lVar = 245 'Returns 4, the number of bytes used to store the variable: MsgBox Len(lVar) 'a variable of type single Dim sVar As Single sVar = 245.567 'Returns 4, the number of bytes used to store the variable: MsgBox Len(sVar) 'a variable of type double Dim dVar As Double dVar = 245.567 'Returns 8, the number of bytes used to store the variable: MsgBox Len(dVar) '---------------------------
Example - Using LEN and MID Functions, to determine characters appearing at odd number positions in a text string. Sub Mid_Len_OddNoCharacters() 'Using LEN and MID Functions, to determine characters appearing at odd number positions in a text string Sheets("Sheet1").Activate Dim str As String, strOdd, i As Integer 'assign text string in cell A2 ("HELLO") to variable (str) str = ActiveSheet.Range("A2").Value 'loop though each character - vba Len function determines the length or number of characters in the text string 'check odd number position 'return character at odd number position, and add it to the string variable srtOdd 'vba Mid function extracts 1 character from str, starting from character number i strOdd = strOdd & Mid(str, i, 1) 'enter the string (variable srtOdd) containing odd positioned characters in cell A3 ("HLO") Range("A3").Value = strOdd Example - Using LEFT, LEN and MID Functions, to return initials of full name. Sub Left_Mid_Len_InitialsOfName()
Dim strName As String, strInitials As String 'assign variable strName to the text string - the first name, middle name(s) & surname with inbetween spaces: 'strName = " Alec Thomas stone Hanson " strName = ActiveSheet.Range("A6").Value
For i = 1 To Len(strName) 'if the first character is not blank space, it will be an initial: If Left(strName, i) <> " " Then 'add dot after first initial strInitials = Left(strName, 1) & "." 'if any character after the first character is preceded by a blank space, it will be an initial: If Mid(strName, i - 1, 1) = " " And Mid(strName, i, 1) <> " " Then 'determines if first initial If Len(strInitials) < 1 Then 'add dot after first initial strInitials = Mid(strName, i, 1) & "." 'for multiple initials, add to the previous initial(s): strInitials = strInitials & " " & Mid(strName, i, 1) & "."
strInitials = UCase(strInitials) 'returns "A. T. S. H." and enter the string variable strInitials in cell A7 Range("A7").Value = strInitials 'returns 11 - 4 letters, 4 dots & 3 blank spaces REPLACE Function (Worksheet) The worksheet REPLACE function replaces part of a text string with a new text string, based on specified number of characters and starting from a specified position. Syntax: REPLACE(old_text, start_number, number_of_chars, new_text). It is necessary to specify all arguments. The old_text argument is the text string in which you want to replace with new text. The start_number argument is the position of the character in old_text, which you want to replace (ie. position of the first character from which replacement should start). Position is the character number, first character being number 1 & incrementing towards the right. The number_of_chars is the number of characters which will be replaced in the old_text (with new_text). The new_text is the text string which will replace the characters in old_text. Example - using the Worksheet Replace Function in vba code to delete blank spaces in excess of a specified number, within a string Function DeleteBlankSpaces(str As String, iSpaces As Integer) As String
For n = Len(str) To 1 Step -1 'if character is blank space If Mid(str, n, 1) = " " Then 'if blank space(s) equal to or less than specified number If counter < iSpaces + 1 Then 'go to next character ie. next n 'if blank space(s) in excess of specified number, then delete 'using the worksheet Replace function to replace with a zero-length string str = Application.Replace(str, n, 1, "") 'if character is NOT blank space, go to next character ie. next n 'restore to 0 if non-blank character
Sub DelBlSp()
Dim strText As String, iSpaces As Integer
strText = ActiveSheet.Range("A10").Value 'specify the maximum number of consecutive spaces to retain within the string - this procedure will delete spaces in excess of this specified Number 'assign this max number to variable iSpaces
'enter final string in cell A11 (" Specify max spaces in text .") of active sheet - call DeleteBlankSpaces function and pass the arguments of string (strText) & specified blank spaces (iSpaces) ActiveSheet.Range("A11").Value = DeleteBlankSpaces(strText, iSpaces) Example - ensure uniform no. of consecutive blank spaces within string, where existing blank spaces are present Function UniformBlankSpaces(str As String, iSpaces As Integer) As String
'counter is used to determine the no. of consecutive blank spaces
For n = Len(str) To 1 Step -1 'if character is blank space If Mid(str, n, 1) = " " Then 'if blank space(s) is less than specified number If counter < iSpaces Then 'if first character of the string is a blank space str = Left(str, 1) & " " & Right(str, Len(str) - n) 'if character preceding blank space at n is NOT a blank Space ElseIf Mid(str, n - 1, 1) <> " " Then str = Mid(str, 1, n) & " " & Right(str, Len(str) - n) 'if character preceding blank space at n is also a blank Space 'go to next character ie. next n 'if blank space(s) in excess of specified number, then delete ElseIf counter > iSpaces Then 'using the worksheet Replace function to replace with a zero-length string str = Application.Replace(str, n, 1, "") 'if character is NOT blank space, go to next character ie. next n 'restore counter to 0 if non-blank character
Sub UniformBlSp()
Dim strText As String, iSpaces As Integer
strText = ActiveSheet.Range("A10").Value 'specify the uniform number of consecutive spaces to retain within the string - this procedure will delete/add spaces in excess/short of this specified Number 'assign this number to variable iSpaces
ActiveSheet.Range("A12").Value = UniformBlankSpaces(strText, iSpaces) REPLACE Function (VBA) The vba Replace Function is used to return a string in which a specified substring is replaced, a specified number of times, with another specified substring. Syntax: Replace(expression, find, replace, start, count, compare). It is necessary to specify the arguments of expression, find and replace, while the start, count & compare arguments are optional. The expression argument is the string in which a specific substring is replaced. For a zero-length expression string a zero-length string is returned, and for a Null expression the function will give an error. The find argument specifies the substring which is to be replaced. If find substring is zero-length, then a copy of the expression is returned. The replace argument specifies that 'another substring' which replaces (ie. the replacement substring). A replace substring of zero-length has the effect of deleting all occurrences of find from the expression. The start argument specifies the position (ie. character number) in the expression from where you want to start your search for the 'find' substring. If omitted, it will default to1 (ie. search will start from first character position). The string returned by the Replace function begins from this start position till the last character of the expression string, after the replacement(s). Specifying a start position which is greater than the length of the expression, will return a zero-length string. The count argument specifies the number of substring replacements you want to do. Omitting to specify this will default to the value -1, which will make all possible replacements. Specifying zero for the count will have the effect of no replacement and will return a copy of the expression. The compare argument specifies the type of comparison to use for evaluating substrings - a numeric value or a constant can be specified herein, as detailed below. You can specify the following arguments for the compare argument: vbUseCompareOption (value: -1) performs a comparison using the setting of the Option Compare statement. vbBinaryCompare (value: 0) performs a binary comparison - string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order - wherein ANSI 1252 is used for English and many European languages), wherein uppercase characters are always less than lowercase characters -> A < B < U < Z < a < b < u < z < À < Û < à < û. vbTextCompare (value: 1) performs a textual comparison - string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z). vbDatabaseCompare (value: 2) performs a comparison based on information in your database (can be used within Microsoft Access only). If you do not specify the compare argument, the comparison is done based on the defined Option Compare statement. Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method - you must specify 'Option Compare Binary' or 'Option Compare Text' at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary. Example - using the vba Replace function. Sub Replace_vbaFunc() Dim str As String, strFind As String, strReplace As String 'find all ocurrences of "s" to be replaced: strFind = "s" 'set replace string as zero-length: strReplace = "" '--------------------------- str = "She was selling sea shells!" 'returns 27: MsgBox Len(str) 'returns the string after deleting all occurences of 's' - "She wa elling ea hell!". Note that capital 'S' is not replaced. str = Replace(str, strFind, strReplace) MsgBox str 'returns 22: MsgBox Len(str) '--------------------------- str = "She was selling sea shells!" 'returns 27: MsgBox Len(str) 'returns the string after deleting all occurences of 's' - "he wa elling ea hell!". 'Note that capital 'S' is also replaced because a textual comparison has been done by setting the compare argument value to vbTextCompare (value: 1). str = Replace(str, strFind, strReplace, , , 1) MsgBox str 'returns 21: MsgBox Len(str) '--------------------------- str = "She was selling sea shells!" 'returns 27: MsgBox Len(str) 'deleting all occurences of 's' from start position 8 - returns " elling ea hell!". Note that the character number 8 is a blank space and this is also returned. 'the string returned by the Replace function begins from this start position till the last character of the expression string. str = Replace(str, strFind, strReplace, 8) MsgBox str 'returns 16: MsgBox Len(str) '--------------------------- str = "She was selling sea shells!" 'returns 27: MsgBox Len(str) 'Specifying a start position which is greater than the length of the expression, will return a zero-length string. str = Replace(str, strFind, strReplace, 28) 'returns a zero-length string: MsgBox str 'returns 0: MsgBox Len(str) '--------------------------- str = "She was selling sea shells!" 'returns 27: MsgBox Len(str) 'The count argument specifies the number of substring replacements you want to do. Omitting to specify this will default to the value -1, which will make all possible replacements. 'specifying a start position of 8, and count of 2: str = Replace(str, strFind, strReplace, 8, 2) 'returns " elling ea shells!", after deleting 's' twice from start position of 8. MsgBox str 'returns 18: MsgBox Len(str) '---------------------------
Example - Manipulate Strings in Excel VBA - Remove Numericals, Delete/Add Blank Spaces, Capitalize letters. To download Excel file with live code, click here. Function StringManipulation(str As String) As String 'String Manipulation using vba & worksheet Replace funcions, Trim / Len / Right / Mid / Chr / Asc / Ucase function. 'This code manipulates a string text as follows:
'Excel uses ANSI character set for Windows computers. The Windows operating environment uses the ANSI character set, whereas Macintosh uses the Macintosh character set, so that the returned character corresponds to this character set. 'chr(48) to chr(57) represent numericals 0 to 9 in ANSI/ASCII Character codes 'remove all numericals from the text string using vba Replace function str = Replace(str, Chr(i), "")
'REMOVE LEADING, TRAILING & INBETWEEN SPACES (LEAVE SINGLE SPACE BETWEEN WORDS)
str = Application.Trim(str)
'ADD SPACE (IF NOT PRESENT) AFTER EACH EXCLAMATION, COMMA, DOT AND QUESTION MARK:
'start from last character in the string and move to the first For n = iTxtLen To 1 Step -1 'Chr(32) returns space; Chr(33) returns exclamation; Chr(44) returns comma; Chr(46) returns dot / full stop; Chr(63) returns question mark; If Mid(str, n, 1) = Chr(33) Or Mid(str, n, 1) = Chr(44) Or Mid(str, n, 1) = Chr(46) Or Mid(str, n, 1) = Chr(63) Then 'check if space is not present after, except for the last Character If Mid(str, n + 1, 1) <> Chr(32) And n <> iTxtLen Then 'using Mid & Right functions to add space - note that current string length is used str = Mid(str, 1, n) & Chr(32) & Right(str, iTxtLen - n) 'update string length - increments by 1 after adding a space (character) - this enables correct use of the Right Function in above line
'DELETE SPACE (IF PRESENT) BEFORE EACH EXCLAMATION, COMMA, DOT & QUESTION MARK:
'exclude the first character For n = iTxtLen To 2 Step -1 'Chr(32) returns space; Chr(33) returns exclamation; Chr(44) returns comma; Chr(46) returns full stop; Chr(63) returns question mark; If Mid(str, n, 1) = Chr(33) Or Mid(str, n, 1) = Chr(44) Or Mid(str, n, 1) = Chr(46) Or Mid(str, n, 1) = Chr(63) Then 'check if space is present before If Mid(str, n - 1, 1) = Chr(32) Then 'using the worksheet Replace function to delete a space: str = Application.Replace(str, n - 1, 1, "") 'omit rechecking the same character again- position of n shifts (decreases by 1) due to deleting a space character:
'determine the ANSI code of each character in the string: Excel uses ANSI character set for Windows computers. The Windows operating environment uses the ANSI character set, whereas Macintosh uses the Macintosh character set, so that the returned character corresponds to this character set. 'The vba Asc function returns the corresponding character code (Integer data type) for the string letter - Asc(string). ansiCode = Asc(Mid(str, i, 1)) '97 to 122 are the ANSI codes equating to small cap letters "a" to "z" 'character no 3 onwards to enable determining if 2 positions preceding it is an exclamation, full stop & question mark 'capitalizes a letter whose position is 2 characters after (1 character after, will be the space character added earlier) an exclamation, full stop & question mark: If Mid(str, i - 2, 1) = Chr(33) Or Mid(str, i - 2, 1) = Chr(46) Or Mid(str, i - 2, 1) = Chr(63) Then Mid(str, i, 1) = UCase(Mid(str, i, 1)) 'capitalize first letter of the string: Mid(str, i, 1) = UCase(Mid(str, i, 1)) 'if capital letter, skip to next character (ie. next i):
'function returns the final manipulated string Sub Str_Man() Sheets("Sheet2").Activate 'specify the text string which is required to be manipulated (" 2manipulate text string8 , by vba Code .check below cell ! ok ?thanks 7 . "), and assign to string variable strText strText = ActiveSheet.Range("A2").Value 'enter manipulated string in cell A3 ("Manipulate text string, by vba Code. Check below cell! Ok? Thanks.") of active sheet - call StringManipulation function and pass the string (str) argument ActiveSheet.Range("A3").Value = StringManipulation(strText) Example - Replace all occurrences of a substring in a string expression, with another substring - using the vba Replace function. To download Excel file with live code, click here. Function Replace_Str1(var As Variant, vFind As Variant, vReplace As Variant, iOption As Integer) As Variant 'Replaces all occurrences of substring (vFind) in the string (var), with another substring (vReplace) - using the vba Replace function. 'position of the first occurrence of vFind, within var: iPosn = InStr(var, vFind)
'if vFind found within var 'use the vba Replace function to replace all instances of vFind with vReplace Replace_Str1 = Replace(var, vFind, vReplace, , , iOption) Sub ReplaceStr1()
Dim var As Variant, vFind As Variant, vReplace As Variant, iOption As Integer
'var = "she Sells sea shellS" var = ActiveSheet.Range("A2").Value 'vFind is the string to search within var & which will be replaced by vReplace: 'vReplace is the string which replace all instances of vFind within var: 'to delete all spaces, use a zero-length string for vReplace 'in a case-insensitive comparison, if you specify to replace occurrences of "a" then instances of both "A" & "a" will be replaced. 'to perform a binary comparison (case-sensitive), use value 0: 'to perform a text comparison (case-insensitive), use value 1:
'if var is Null, exit procedure MsgBox "var is Null, exiting procedure" 'if vFind is Null or a zero-length string: If IsNull(vFind) Or vFind = "" Then 'return var without any replacements & exit procedure: MsgBox "Either vFind is Null or a zero-length" ActiveSheet.Range("A3") = var 'if var or vFind are not Null, and vFind is not zero-length, replace all instances of vFind ("?he ?ell? ?ea ?hell?"): ActiveSheet.Range("A3") = Replace_Str1(var, vFind, vReplace, iOption) InStr & InStrRev Functions (VBA) The InStr Function returns the position (character number) in which a string first occurs within another string. Syntax: InStr(start, string, substring, compare). It is necessary to specify the arguments of string and substring, while the start & compare arguments are optional. The start argument specifies the position (ie. character number) within string from where you want to start your search for the substring. It is necessary to specify the start argument, if the compare argument is to be specified. If omitted, it will default to1 (ie. search will start from the first character position). Specifying a start position which is greater than the length of string will return a 0 (zero), and if start contains Null an error will occur. The string argument is the string expression within which substring is being searched. The function returns 0 if string is zero-length, and returns Null if string is Null. The substring argument is the string expression which is being searched within string and whose position will be returned by the function. The function returns 0 if substring is not found, returns the start value if string is zero-length, and returns Null if substring is Null. The compare argument specifies the type of comparison to use for evaluating strings - a numeric value or a constant can be specified herein, as detailed below. You can specify the following arguments for the compare argument: vbUseCompareOption (value: -1) performs a comparison using the setting of the Option Compare statement. vbBinaryCompare (value: 0) performs a binary comparison - string comparisons based on a binary sort order (in Microsoft Windows, the code page determines the sort order - wherein ANSI 1252 is used for English and many European languages), wherein uppercase characters are always less than lowercase characters -> A < B < U < Z < a < b < u < z < À < Û < à < û. vbTextCompare (value: 1) performs a textual comparison - string comparisons which are not based on a case-sensitive text sort order -> (A=a) < (À = à) < (B=b) < (U=u) < (Û = û) < (Z=z). vbDatabaseCompare (value: 2) performs a comparison based on information in your database (can be used within Microsoft Access only). If you do not specify the compare argument, the comparison is done based on the defined Option Compare statement. Option Compare Statement (viz. Option Compare Binary or Option Compare Text) can be used to set the comparison method - you must specify 'Option Compare Binary' or 'Option Compare Text' at the module level, before any procedure. If the Option Compare Statement is not specified, the default text comparison method is Binary. The InStrRev Function returns the position (character number) in which a string first occurs within another string, starting from the end of that another string. Syntax: InStrRev(string, substring, start, compare). Use InStrRev function instead of InStr to search in the reverse direction. It is necessary to specify the arguments of string and substring, while the start & compare arguments are optional. If start is omitted, -1 is used, meaning that the search will begin from the last character position. All other syntax explanations remain same as in the InStr function. Example - Using the InStr function. Sub InStrFunc() Dim str1 As String, str2 As String '----------------- str1 = "she sells sea shells" str2 = "e" 'returns 3: MsgBox InStr(str1, str2) '----------------- str1 = "she sells sea shells" str2 = "e" 'returns 6: MsgBox InStr(4, str1, str2) '----------------- str1 = "she sells sea shells" str2 = "e" 'returns 0 because start value (24) is greater than length of str1 (20): MsgBox InStr(24, str1, str2) '----------------- str1 = "" str2 = "e" 'returns 0 because str1 is zero-length: MsgBox InStr(str1, str2) '----------------- str1 = "she sells sea shells" str2 = "f" 'returns 0 because str2 is not found: MsgBox InStr(str1, str2) '----------------- Dim str3 As Variant str1 = "she sells sea shells" str3 = Null 'returns 1, indicating subtype Null - because str3 is Null: MsgBox VarType(InStr(str1, str3)) '----------------- str1 = "she Sells sea shells" str2 = "s" 'returns 9 - the default text comparison type is Binary (case-sensitive): MsgBox InStr(2, str1, str2) 'returns 5 - performing a textual comparison wherein comparison type is not case-sensitive: MsgBox InStr(2, str1, str2, 1) '-----------------
Example - Replace all occurrences of a substring in a string expression, with another substring - using the InStr, Left & Mid functions. To download Excel file with live code, click here. Function Replace_Str2(var As Variant, vFind As Variant, vReplace As Variant) As Variant
iPosn = InStr(var, vFind) 'length of vFind, which will be replaced with vReplace: 'length of vReplace, which will replaced vFind: iReplaceLen = Len(vReplace)
'return var string & exit procedure: 'if vFind found within var: 'replace vFind with vReplace within var - use the Left & Mid functions to return string. Omitting character nos from the Mid function will return the text string in full from start number position to end of the text string. var = Left(var, iPosn - 1) & vReplace & Mid(var, iPosn + iFindLen) 'position of the first occurrence of vFind within updated var, starting the position search from the first character AFTER the latest replacement (iPosn + iReplaceLen) iPosn = InStr(iPosn + iReplaceLen, var, vFind) 'if vFind not found within updated var, exit loop If iPosn = 0 Then Exit Do Sub ReplaceStr2()
Dim var As Variant, vFind As Variant, vReplace As Variant
'var = "she Sells sea shellS" var = ActiveSheet.Range("A6").Value 'vFind is the string to search within var & which will be replaced by vReplace 'vReplace is the string which replace all instances of vFind within var:
MsgBox "var is Null, exiting procedure" 'if vFind is Null or a zero-length string: If IsNull(vFind) Or vFind = "" Then 'return var without any replacements & exit procedure MsgBox "Either vFind is Null or a zero-length" ActiveSheet.Range("A7").Value = var 'if var or vFind are not Null, and vFind is not zero-length, replace all instances of vFind ("?he Sell? ?ea ?hellS") ActiveSheet.Range("A7").Value = Replace_Str2(var, vFind, vReplace) |
Como comparar com valor nulo vba excel
Postagens relacionadas
direito autoral © 2024 ifazerum Inc.
