Posts by nomel

Re: Convert Byte Array Containing Floating Point To Single

Ahhh!!! Perfect! Much much much faster.

I'm dealing with 4 million data points...so....thank you!!!!!!!!!!!!

Re: Convert Byte Array Containing Floating Point To Single

ohhh...very nice shg!! I didn't realize you could convert the fraction that way.

One problem though, you have to change the exponent to -126 if it's denormalized.

The denormalized hex value 00400000h should be come out to be about 5.9×10^-39, but with your function, it is about 2.9X10^-39.

Besides that, I'm using yours!

Re: Dynamically Update Chart Source Range

I found the problem! I removed some references and the same macro started working.

Re: Convert Byte or Integer To Floating Point Number

Yes, while running to get coffee between tests in the lab. Still my mistake, I'll try to be more courteous.

Re: Convert Byte or Integer To Floating Point Number

I haven't said thanks yet because I'm still at work and have been very busy.

Thanks for the interest and help mike. The simplified description I gave was simplified to just help explain what the standard defined, not for implementation. There are other details (in the link I provided) like normalization, and the significant must be converted to a binary fraction before that equation can be used. Those pesky little details And that example I made up ends up being around 6.06x10^-25. So small

Here is the finished function. I've tested it against the examples on the wikipedia page I linked to:

Function ByteArrayToSingle(data() As Byte, offset) As Single
'converts a byte array of an ieee 754 single type floating point numbers to a single.
'data byte array contains the 32 bit single numbers, starting at offset
'Description and details: http://en.wikipedia.org/wiki/IEEE_754#Single-precision_32_bit
'NOTE! Byte order could be different on strange systems/equipment (like a scope)!
Dim n As Single     'this is the resulting single number
Dim frac As Double  'fractional binary number from significant
Dim e As Integer    'exponent
Dim sig As Long     'significant integer
Dim i As Integer    'for for loops and whatnot


  'get exponent
  e = (data(offset) And 127) * 2 '127 = 0111111  'isolate first 7 bits, then shift left one.
  e = e Or Int((data(offset + 1) And 128) / (2 ^ 7)) 'lsb is in next byte...isolate and put in e.
  e = e - 127   'e is biased by 127 so it can stay unsigned (always positive)


  sig = 0
  sig = data(offset + 1) * 2 ^ 16   'shift left 16.
  sig = sig Or (data(offset + 2) * 2 ^ 8) 'shift over 8 and put second byte in sig
  sig = sig Or (data(offset + 3))  'put third byte in sig.


  frac = 1 'start with 1 so result is 1.<fraction>
  If e = -127 Then 'denormalized (0.fraction rather than 1.fraction)
    e = -126
    frac = 0  'start with 0 so result is 0.<fraction>.
  End If


  'calculate binary fraction from significant.
  For i = 22 To 0 Step -1  'step through all 23 bits.
    If (sig And 2 ^ i) > 0 Then frac = frac + 2 ^ -(23 - i) 'if the bit is a 1, the number will be non zero.
  Next


  n = (2 ^ e) * frac 'calculate the final number
  If (data(offset) And 128) > 0 Then n = -n 'if sign bit is set, make negative.
  
  ByteArrayToSingle = n 'return result


End Function

What a terribly inefficient and backwards way to get a number that's already in memory back into memory! :roll:

Re: Convert Byte or Integer To Floating Point Number

like i said...the details can be googled...or you could follow the link i provided at the beginning.

Re: Convert Byte or Integer To Floating Point Number

the IEEE 754 standard (wikipedia link) is the standard that describes how floating point numbers are stored in a computers memory. having a standard allows programs to read numbers sent by other programs or computers.

each variable is stored in the computers memory as a binary number.
for ascii strings, this is something like
[integer number of chars][byte][byte][byte][byte][byte]...etc. where each byte represents the acsii code for the letter (see asc and chr functions).

for numbers, storing as a string wouldn't make any sense, you would be using a whole byte (a number between 0 and 255) to store a value that is only between 1 and 10! So instead, the number is stored in binary, base 2 rather than base 10. for integers, this is fairly direct since any integer can be converted to a binary number. 255d = 11111111b, 56d = 111000b, etc. But what happens if you have a fraction of a number, or a fraction of a number plus an integer (like 3 + .14159 = 3.14159)?

That's where floating point comes in. It allows you to take an integer number (24 bit number for single), then *move* the decimal place to nearly wherever you want it...a *floating* decimal place...a floating point number! The standard describes how to store the information for where the decimal place goes, etc. Slightly simplified, it is in the following format:

[sign bit (+/-)][8 bit decimal place position][23 bit integer]

in this simplified example, for the number 5.12342134, the 24 bit integer (the first bit is always 1, and is hidden) would contain 512342134, and the decimal place position byte would say to put the decimal place after the 5. the sign bit would be 0 for positive. this gives the 32 bits total is how single type numbers are stored in memory.

the details can be googled.

i'll post the function i'm making to convert that byte array in a bit

Re: Convert Byte or Integer To Floating Point Number

it's a standard IEEE 32 bit floating point number...the same type used in excel for single. the data in the byte array is the data from memory for the number...how the single is actually stored in the computers memory as ones and zeros. not sure how giving an example will help cause it's an arbitrary bit pattern depending on the number....but here you go:

dim data(3) as byte
dim number as single


'i usually get these values from a piece of equipment...
data(0) = 23
data(1) = 59
data(2) = 120
data(3) = 12
'so the whole binary representation for the number is '0001'0111'0011'1011'0111'1000'0000'1100'...or something close...order of significance could be off.


'now data contains my 4 byte floating point number. who knows what the
'number actually...thus the reason for my desire to convert it to a number i can understand.


'to convert it to single.....
number = somefunction(data)  'this function converts it to a single.

so, anyone know what somefunction is? i'm probably the only person that's ever needed to do this in excel I have a feeling I'll have to study the IEEE standard and make a function myself.

Re: Convert Byte or Integer To Floating Point Number

this is not the ascii representation, it's a binary representation of the floating point number.

the byte array will contain the 32 bit representation of a floating point number, so a total of four elements. the output will be a single. so something like

function bytetosingle(bytearray() as byte) as single

in vb.net, this function exists. It is BitConverter.ToSingle(bytearray() as byte, offset).

in delphi or c, I would use a memory pointer, but I'm not sure how to do it in vb. any ideas, or do I have to get all low level (and very slow)?

Hello,

I have a byte array that contains a 4 byte floating point number. How would I convert this byte array to the single typed floating point number it contains?

Thanks for any help!

Re: Custom Moving Focus In Exel

If you open the worksheet in the vba editor, you can select the worksheet and the change method from the dropdown lists at the top. This is called every time a cell changes. It tells you the range of all the cells that have changed.

Putting the following code will do what you described...but only when a change occurs...just pressing return in row 1 or 5 will do nothing, you have to change the contents of a cell in row 1 or 5, then press return.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Count = 1 Then  'make sure only one cell is selected...if there's a range, then, ignore.
   Select Case Target.Cells.Item(1).row   'get the row of the first (and only) cell in the selection.
     Case 1 'change happened in row 1
       Cells(4, Target.Cells.Item(1).Column).Select
     Case 5 'change happened in row 5
       Cells(6, 1).Select
   End Select
 End If
End Sub

You can have it detect a change in selection using the selectionchange method of the worksheet, but, it gets messy since it triggers any time a selection happens.

I don't know of an easy way to detect a keypress...but maybe someone else does.

Re: Setting Category Labels On A Created Chart

*bump*

Re: Dynamically Update Chart Source Range

Isn't making a named range the same as setting a label to a range? I don't see why that would work if these methods (putting in a direct range rather than a label to the same range) don't work. I think it's more to do with syntax, since even recording a macro that changes the category labels and playing it back fails with an error.

Re: Creating Selection List

Yes you could do it using the worksheet selection change event.
Each time the selection is changed (cell is clicked) it will give you the range of the selection (the cell if only a cell is clicked). From there, you could show a userform (formname.show) that you added the inventory selections too, then just set the cell to the total cost and close the form after clicking an 'ok' button on the form.

Re: Dynamically Update Chart Source Range

What do you mean?

Did you look at my examples of what I have used?

Re: Setting Category Labels On A Created Chart

Quote from Sicarii

something like?

Code

With ActiveChart
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "This is X"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "This is Y"
    End With

Not quite. I'd like the category labels, not the axis titles. If you look at the source data properties of a chart, and go to the series list, it's the section called "category (X) axis labels" under the series data.

I have tried recording a macro where I select this option in the source data properties window, then select the cells as usual.

It gives the following code:

ActiveChart.SeriesCollection(1).XValues = "='LineReg Graphs'!R9C1:R23C1"

When I play it back it gives me an error! So, even excel doesn't know how

Not sure what to do. I'm sure SOMEONE has run into this before.

Re: Format Total Hours to Days, Hours & Minutes

if you know the hours,
mod(hours,23) will give you days, (hours - mod(hours,23)*24) will give you the correct hours.

Re: Insert Data From Form Into Spreadsheet

"i have a user form in my spreadsheet that uses option buttons and text boxes for user entry. i need to take the values and true false entries from the option buttons and place them in cells."

This should get you started.

sub DoStuff()
dim row
dim col


  row = 1   'these can be any value.
  col = 1


  'for checkboxes, the .value property is true or false.
  if formname.checkboxname.value then   'check if it's true
    cells(row,col) = "Yes"   'true, so put yes in the cell on the active worksheet.
  else
    cells(row,col) = "No"    'false, so put no in the cell.
  endif
end sub

Re: Automate Chart With Regular Row Increments

If you noticed, the chart wizard was used with an existing chart (created one line before).

Again, *look up* and *use* the chart wizard. It lets you set the title, source data, etc.

activechart.chartwizard(blah blah)

Hello,

How do I set the category labels on a chart I've created with chartobjects.add?

Thanks!