'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. Or forget trying to use an "Excel formula", and go straight to VBA with with its random function counterpart w = CLng(Rnd() * 0.3 + 0.Cast to long or cast to int, be aware of the following. Or is there some other reason I've missed? Solution 2 W = Application.Evaluate("=RAND() * 0.3 + 0.35")Īs to the formula itself, why this construct? It will return Single in the range [0.35, 0.65) which when rounded to a Long will return 0 or 1 at 50% probability of each. If its to emulate a cell formula use Dim w as Long If its to get a long integer result from the formula =RAND() * 0.3 + 0.35, use Dim w as Long What are you actually trying to achieve here? the string "=RAND() * 0.3 + 0.35" itself does not look like a number, even though it will evaluate to a number. The root cause of your error is that CDbl expects a numeric value or a string that looks like a number.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |