![]() If ValidMinus Then 'One has already been detected and this is a duplicateĮrrMsg = "Invalide entry.too many minus signs! "ĮlseIf x = 1 Then 'if in the first position it is validĮrrMsg = "Invalide entry.Minus sign not in the correct position! " If ValidPlus Then 'One has already been detected and this is a duplicateĮrrMsg = "Invalid entry.too many plus signs!"ĮlseIf x = 1 Then 'if in the first positon it is valideĮlse 'Not in first position and it is invalidĮrrMsg = "Invalide entry.Plus sign not in the correct position! " 'It checks to make sure the + and - are the first character if entered and no duplicates. 'This function checks the string entry to make sure that valid digits are in the string. Function IsValidNumericEntry(MyString As String) As Boolean First checks the string for a proper numeric format, second and third function converts a string to Long or Double. Here are a three functions that might be useful. Change the function return type, and rename all occurrences of the function variable to make everything consistent. Just replace the conversion function itself (CLng). The above code can be modified to handle conversion from string to-Integers, to-Currency (using CCur() ), to-Decimal (using CDec() ), to-Double (using CDbl() ), etc. It's common to have an integer outside of that range in spreadsheet operations. I chose Long (Integer) instead of simply Integer because the min/max size of an Integer in VBA is crummy (min: -32768, max:+32767). If Err.Number = 13 Then 'error # 13 is Type mismatch 'Otherwise, disable the error handler, and re-run the code to allow the system to 'If the error is type-mismatch, clear the error and return numeric 0 from the function 'IF we've reached this point, then we did not succeed in conversion ![]() With this function: Function ConvertToLongInteger(ByVal stValue As String) As LongĬonvertToLongInteger = CLng(stValue) 'TRY to convert to an Integer valueĮxit Function 'If we reach this point, then we succeeded so exit If you require functionality for these kind of cases, then you have to check for another solution.ĬurrentLoad = ConvertToLongInteger(oXLSheet2.Cells(4, 6).Value) Of course you can also think of cases where people use commas and dots, e.g., three-thousand as 3,000.00. ' to prevent that value is too high or too low. ' similar function for cast-int, you can add minimum and maximum value if you like So I recommend to use the following alternative: Public Function CastLong(var As Variant) So if you use a comma in your double like in some countries in Europe, you will experience an error in the US.Į.g., in european excel-version 0,5 will perform well with CDbl(), but in US-version it will result in 5. These functions are one of the view functions in Excel VBA that are depending on the system regional settings. ![]() Cast to long or cast to int, be aware of the following. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |