![]() If Mid$(strBin, I, 1) = "1" Then strBin2dNum = strBin2dNum + 2 ^ (Len(strBin) - I)įunction dNum2strHex(ByVal dblNum As Double) As Stringĭim dRest As Double, I As Integer, S As String * 1 Tst = Right$(String$(3, "0") + Left$(strBin, n), 4) End Ifįunction strBin2dNum(ByVal strBin As String) As Double 'something here to strip any prefixing format 'Option Explicit is set at top of routines ![]() Public Function strBin2strHex(strBin As String) As String If the value is 0, 4 bytes is received, but wouldn't it be nicer if the returned string is always a multitude of of 4? (and in the future perhaps adding dots, like 0101.1100) Following that method prevents having to start from 64^2 downwards until the code has to start. since you are looking if a byte is 1 in a number, you can easily use an AND comparisson, instead of substracting. if you're afraid you can't call the function using something like dNum2strBin("5"), you can declare the parameter byval. First thing of course is the same as above: undefined variables. (didn't really look further yet, started by alphabet -) ) If Len(strBin) = 0 Then strBin = String$(4, "0") 'Accepts a Double Number and Returns a formatted Binary String GetFormat(strTest, uFormat) 'This routine returns uFormat which represents the number format of the data in strTest GetBinary(uNum, uFormat, strBin) 'Returns a Binary String without the leading 'Back Tick'. Restore(strBin, uNum, uFormat) 'Restores a Shifted Number back to it's original formatting ShiftLeft(uNum, Places, Carry As String, WW) 'WW - Word Width in Bits ShiftRight(uNum, Places, Carry As String, WW) 'WW - Word Width in Bits StrOct2dNum(strOct) 'Converts a Formatted Octal String to a Double Number StrHex2dNum(strHex) 'Converts a Formatted Hex String to a Double Number Using singles will very rarely be of any benefit because VBA internally converts them into doubles in calculations anyway, so they are actually slower than using doubles in the first place, as well as losing precision.Here are a few routines I've been playing with in Excel.īTW, I also have an Engineering Notation routine, If Interested.ĭNum2strHex(dNum) 'Converts a Double Number to a Formatted Hex StringĭNum2strOct(dNum) 'Converts a Double Number to a Formatted Octal StringĭNum2strBin(dNum) 'Accepts a Double Number and Returns a formatted Binary String In general it pays to minimise the number of data transfers between VBA and the worksheet. Note that if you do that any changes to MyData will also be displayed in "myrange" in the worksheet, whereas if you convert the data to a variant array and want the changed data to display in the worksheet you have to do:Īlso note that having the active link between VBA range objects and the worksheet range can be very slow if a lot of small changes are made. If you are doing complex calculations on a huge array it can sometimes be worthwhile to convert the variant array into a double array, but that is rarely worth the trouble.įor your purposes it may be better to keep the range as a range object: Value2 is significantly faster and I recommend it as the default. Value2 converts all numerical values into doubles (but it is still a variant array, because it can contain strings). ![]() These will (if "myrange" is a valid named range in the active workbook) create an array of the values in myrange. It is often worth converting a range object into a variant array, which can be done with: ![]() Currency also uses 64 bits, but is stored in a different format appropriate to currency calculations. For any value other than currency the value will have double precision (64 bits). It can be a number, a string, a range, or any other object.įor your purposes the important point is that if you are working with the value property of cells in a range (which you are if you use worksheetfunction.sum) then the values will be variants, but they will have the same precision as the data in the cells, so you can confidently use variants without worrying about loss of precision. As has been said, a variant is holder for anything.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |