Wednesday, April 5, 2017

Excel VBA read LabView date time stamp

To read a binary file you have to know the structure of it. In my case I was trying to read binary file written by LabVIEW-based software which records measurement data. The file represent a one-dimensional array of clusters, the cluster has two elements: TimeDate stamp and double precision number.

According to the following link

LabVIEW 7.0 or earlier used a 64-bit double (DBL) to represent time, yielding 15 digits of precision. The number of seconds between 1st Jan 1904 (the time stamp Epoch or year zero) to 1st Jan 2000 is 3027456000. Representing this as a DBL would use 10 out of the 15 digits of precision. That leaves a very small resolution space to perform hardware timings using most of the resolution by simply going from 1904 to today.  Representing time as a DBL was not ideal since it did not meet industry requirements.

In MS office the date reference is year 1900, while LabVIEW date reference is year 1904. So, in calculations we will compensate this date reference difference. Number of days difference is 1462 days.

' Function to convert binary to decimal
Function BinaryToDecimal(ByVal Binary As String) As Double
Dim BinaryNum As Double
Dim BitCount As Integer
For BitCount = 1 To Len(Binary)
BinaryNum = BinaryNum + (CDbl(Mid(Binary, Len(Binary) - BitCount + 1, 1)) * (2 ^ (BitCount - 1)))
Next BitCount
BinaryToDecimal = BinaryNum
End Function

' Function to convert 64-bit binary to double-precision float
Function BinaryStringToDouble(ByVal BinaryString As String) As Double

Dim i, Sign, Exponent, BitCounter As Integer
Dim Fraction, DoubleNo As Double

'Read number sign
Sign = (-1) ^ CLng(Mid(BinaryString, 1, 1))     'Most-significant bit

' Read exponent
Exponent = BinaryToDecimal(Mid(BinaryString, 2, 11))

' Read the fraction
Fraction = 0
BitCounter = 0
For i = 13 To Len(BinaryString)
BitCounter = BitCounter + 1
Fraction = Fraction + (2 ^ (-BitCounter)) * CDbl(Mid(BinaryString, i, 1))
Next i
BinaryStringToDouble = Sign * (1 + Fraction) * 2 ^ (Exponent - 1023)

End Function

' Function to convert LabView date-time-stamp to string date and time
Function DoubleToDateTime(ByVal LVDateTimeStamp As Double) As String    ' input LabVIEW DateTime stamp (64-bit double precision number)

Dim RefOffset As Double
Dim MSDateTimeStamp As Double
Dim MSDate As Double
Dim DateString As String
Dim DayElapsedTime_sec, Hours, Minutes, Seconds As Double

RefOffset = 126316800     'Reference offset in seconds 1462[days]*24[h/day]*60[Min/h]*60[sec/Min]

'Convert it to Microsoft DateTime stamp: number of seconds from 1-Jan-1900
MSDateTimeStamp = LVDateTimeStamp + RefOffset

MSDate = Application.WorksheetFunction.Floor(MSDateTimeStamp / 86400, 1)    ' number of days from 1900
' 86400: number of seconds per day

DateString = CStr(CDate(MSDate))

DayElapsedTime_sec = MSDateTimeStamp - MSDate * 86400+7200    'Egypt time = UTC time + 2 hours (7200 sec)

Hours = Application.WorksheetFunction.Floor(DayElapsedTime_sec / 3600, 1)

Minutes = Application.WorksheetFunction.Floor((DayElapsedTime_sec - Hours* 3600) / 60, 1)

Seconds = DayElapsedTime_sec - Hours * 3600 - Minutes * 60

DoubleToDateTime = DateString + " " + CStr(Hours) + ":" + CStr(Minutes) + ":" + CStr(Round(Seconds, 0))

End Function



VBA Binary file read

Read binary file written by LabVIEW

Parse (parsing) binary file

Read LabVIEW binary file using Excel VBA

Read data in binary file

Read IEEE754 Double precision 64-bit number from binary file

LabVIEW DateTime stamp to binary

Sunday, April 2, 2017

Involute spur gear design

For the purpose of reversed engineering of spur gear with involute profile, this calculator will help you calculate the different parameters of spur gear.

In most cases, number of teeth is a number that 360 can be divided by: 3,4,5,6,9,10,12,15,18,20,24,30,36,... etc

Smaller module means smaller teeth and vise versa

Gear base tangent method (David Brown tangent comparator):

Inputs: Total number of gear teeth:

Measured span [mm]:

Number of gear teeth within the span:

Pressure angle [deg]:

Outputs: Standard module [mm]:

Addendum [mm]:

Dedendum [mm]:

Outer diameter (Addendum diameter) [mm]:

Pitch circle diameter [mm]:

Base circle diameter [mm] from which the involue profile will be drawn:

Root circle diameter [mm]:

Clearance [mm]:

Fillet radius (recommended) [mm]:

Four-points distance (for even teeth count) [mm]:

Angular pitch [deg]:

Involute angle upper limit (used in parametric equation):


Online spur gear calculator

Draw old spur involute gear - Measure old spur involute  gear - Replace old gear

Robot draw gear

Gear base tangent method - Gear span measurement

Involute profile spur gear calculator

Draw involute spur gear in solidworks


Saturday, April 1, 2017

Insert transparent picture in Excel dialog sheet

Since there is no straight-forward method to insert a transparent-background picture (like .png) in Excel dialog sheet form; this is how to insert transparent picture in Excel dialog sheet:

Two transparent pictures in dialog form

[1] In a normal worksheet, insert the transparent picture

[2] Copy the picture from the worksheet

[3] Open the dialog sheet

[4] In the ribbon, select "Home" tab

[5] As shown below, click on dropdown arrow of "Paste" button, then select "Paste special"

[6] Select paste as "Picture (PNG)"

Now, it is done ...