Monday, May 1, 2017

Prolific USB driver for Windows 10

The latest driver is not compatible with Windows 10. If you update the using device manager, it will install the latest version which is not compatible. After some internet search, the following driver is compatible with Windows 10, you can download it from this link

Tags:

HXSP-2108D driver for Windows 10

Prolific USB driver for Windows 10

RS232 TO USB driver for Windows 10


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 http://www.ni.com/tutorial/7900/en/

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


References:

http://www.binaryconvert.com/

https://en.wikipedia.org/wiki/Double-precision_floating-point_format

Keywords:


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):






Keywords:

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

References:

http://www.engineersedge.com/gear_formula.htm

https://en.wikipedia.org/wiki/Involute



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 ...

Sunday, March 5, 2017

PowerPoint VBA: play sound file programatically

On the run, In this post I will show a very simple way to run a sound file programmatically in PowerPoint VBA through the following steps:

[1] Insert an action button of type "Custom". Place it outside the slide area, so it will not be visible during the presentation.

[2] Right click the action button, a popup menu will appear. Select "Hyperlink..." and a new window named "Action settings" will appear.

[3] In "Action settings" window, check the option "Play sound" and select the .wav sound file you want to play from your computer. In most cases you will not have a .wav file so you have to convert to this format. You can do this online through this link Online converter

[4] Rename your shape from selection pane: select the action button, select "Format" tab in the ribbon, go to "Arrange" group and click on "Selection pane" button. In my case, I named the button "Dummy Button".

[5] Finally, It is the code time. You can play the sound with only a single line of code.


' Place this code in slide code
' Sub to play your pre-loaded sound file programmatically
Sub PlayMySound()
Shapes("Dummy Button").ActionSettings(1).SoundEffect.Play
End Sub


Or, instead of step 3, you can also load the sound file programmatically like the next code:


' Place this code in slide code
' Sub to load and play sound file programmatically
Sub LoadPlayMySound()
Shapes("Dummy Button").ActionSettings(1).SoundEffect.ImportFromFile ("C:\Users\Shady\Desktop\Finger-snap.wav")

Shapes("Dummy Button").ActionSettings(1).SoundEffect.Play
End Sub

Done...



Friday, March 3, 2017

Set and get slide Activex controls properties from module


For PowerPoint VBA, there are two ways to control (access) ActiveX controls in certain slide from standard module.

Assume we want to get or set the value of toggle button, then we can do this using one of the following worked examples:


Example 1:

In module code, use the following code:

' Declare a public variable for toggle button value in module code
Public ToggleBtnValue As Boolean

' Sub-routine to call another sub-routine in slide 1
Sub SetToggleBtnValue()
ToggleBtnValue=True
Call Slide1.UpdateToggleBtn
End Sub

In slide 1 code, use the following code:

' In slide 1 code
Sub UpdateToggleBtn()     'Don't use "Private Sub"
MyToggleBtn.Value= ToggleBtnValue
End Sub


Example 2:

In module code, use the following code:

' Read toggle button value in slide number 1
ToggleBtnValue=SlideShowWindows(1).Presentation.Slides(1).Shapes("MyToggleBtn").OLEFormat.Object.value

' Set value of toggle button in slide number 1
SlideShowWindows(1).Presentation.Slides(1).Shapes("MyToggleBtn").OLEFormat.Object.value=False




The previous methods can be used to read or write any of the ActiveX control properties like back color, font name, font size, ... etc.


Monday, February 20, 2017

Excel VBA array of collections






'Try this code in module
 
Dim ArrayOfCollections(100) As Collection
 
Sub AssignValues()
 
Dim Collection1 As New Collection    'Declare the first collection
 
Collection1.Add "Element [1] in collection [1]"
Collection1.Add "Element [2] in collection [1]"
 
' Set array value to the predefined collection
Set ArrayOfCollections(1) = Collection1
'ArrayOfCollections(1) = Collection1     'This code will return error, don't use it
 
' Show values of the first array element
For Each Element In ArrayOfCollections(1)
MsgBox (Element)
Next Element
 
Dim Collection2 As New Collection    'Declare the second collection
 
Collection2.Add "Element [1] in collection [2]"
Collection2.Add "Element [2] in collection [2]"
Collection2.Add "Element [3] in collection [2]"
 
Set ArrayOfCollections(2) = Collection2
 
' Show values of second array element
For i = 1 To ArrayOfCollections(2).Count
MsgBox (ArrayOfCollections(2).Item(i))
Next i
 
End Sub
  

Tags:

Access-Excel-Power Point-Word VBA array of collections

Access-Excel-Power Point-Word VBA collection of collections

Access-Excel-Power Point-Word VBA non-uniform array

Access-Excel-Power Point-Word VBA structured list

 

Sunday, February 12, 2017

Battleship game



Probability matrix:

Probability matrix is a two-dimensional matrix that represents the probability of each cell to be a member of any ship size. In other words, the probability of any cell is the possible number of times it can be a member of any ship. During the game the probability matrix is updating. The minimum value of probability is zero while the maximum value is 34.

The image below shows the probability matrix at the start of the game before any shooting.



As an example, the following animated gif image shows how the probability of cell A1 is calculated for the probability matrix shown above:

5-cell ship:
Number of probabilities to be placed horizontally in single row=6
Number of probabilities to be placed horizontally=6*10=60
Number of probabilities to be placed vertically=6*10=60
Total number of probabilities=120

4-cell ship:
Number of probabilities to be placed horizontally in single row=7
Number of probabilities to be placed horizontally=6*10=70
Number of probabilities to be placed vertically=6*10=70
Total number of probabilities=140


3-cell ship:
Number of probabilities to be placed horizontally in single row=8
Number of probabilities to be placed horizontally=6*10=80
Number of probabilities to be placed vertically=6*10=80
Total number of probabilities=160


2-cell ship:
Number of probabilities to be placed horizontally in single row=9
Number of probabilities to be placed horizontally=6*10=90
Number of probabilities to be placed vertically=6*10=90
Total number of probabilities=180

Number of combinations of placement of ships in Battleship game= 180*160*160*140*120=77,414,400,000



Efficiency measures:

The minimum number of shots possible to finish battleship game for the luckiest person on earth is 17 (20 if the ship's will not be declared).

The maximum number of shots possible to finish battleship game for the most stupid person on earth is 100.

Small ships consumes more shots to detect. So, the strategy aims to hit the largest ships first.

The maximum number of shots to get the first correct hit should be 20. If your technique reach the first hit after this number of iterations then it is not efficient. You can randomly select one of the predefined cells below to shorten number of iterations. The following is valid if all cells of this technique are shot:




Probability to hit ship

Size in blocks

Probability

5

100%

4

80%

3

60%

2

40%



The following cells pattern can be used to detect 4-blocks ship size with the given probabilities:




Probability to hit ship

Size in blocks

Probability

5

100%

4

100%

3

75%

2

51.11%



The following cells pattern can be used to detect 3-blocks ship size with the following probabilities:




Probability to hit ship

Size in blocks

Probability

5

100%

4

100%

3

100%

2

66.67%




The maximum number of shots for the worst-luck person to finish a game should be 75 shot (using checker board strategy of 50 shots, with no ships placed on borders, no ships adjacent to each other, ships detected 2,3,3,4,5 successively). If number of shots is more than 75, then the technique is stupid. For the versions where the player should also recognize the ship size (ship size is not declared), the number should be 79.


The following is valid if all cells in checker board  strategy are shot:

[1] The probability of first hit of 5-blocks ship is 100%
[2] The probability of first hit of 4-blocks ship is 100%
[3] The probability of first hit of 3-blocks ship is 100%
[4] The probability of first hit of 2-blocks ship is 100%




Cell status:

The possible status for any cell in this game is shown below:

Unknown: a cell that has not been shot yet

Missed: a cell that has been shot, but it is empty


Hit: a cell that has been shot and it is not empty

Sunk: a cell that is a part of sunk ship

Blocked: a cell that lies in the surrounding of sunk ship (this status is used in game version where the ship size is not declared)

Game ID:

The game identifier is a unique identifier that describes the game. It describes where the ships were positioned, their orientations, and the locations of shots. For the purpose of benchmarking of the code, I created this identifier for each game played, so I can build results based on unique games.

The following image shows the maximum possible number of shots (iterations) for each ship size once one block is hit:




If one shot is correct then, set the status to "Partial hit" select the neighboring cell with maximum probability. Shot neighboring cells until the whole ship is sunk.

If two adjacent cells are correct hit, then the orientation of ship is known. Select the neighboring cell with maximum probability on the same row or column

The ship is then sunk when one of the following takes place:

[1] All neighboring cells are missed or blocked
[2] Number of hit cells equals the maximum size of unknown ships
[3] Total probability of neighboring cell equals zero

After ship is sunk, get its size, and remove it from the unknown ship sizes.

After ship is detected (and not declared), mark the cells around as blocked.

After a ship is detected, you can define cells that will detect the maximum available ship size.

Number of vertical probabilities for a given ship size to pass through certain cell=Minimum(No. of allowed cells on top+1, No. of allowed cells on bottom+1, Ship size-No. of hit cells).

Number of horizontal probabilities for a given ship size to pass through certain cell=Minimum(No. of allowed cells on left +1, No. of allowed cells on right +1, Ship size-No. of hit cells).

The total probability for a give ship size=Vertical probabilities+Horizontal probabilities

The total probability for all unknown ship sizes is then the summation of total probabilities for all unknown ship sizes.

Where the acronym "allowed cells" are those cells having the status "Unknown" or "Hit".

Note: the previous formula is applicable where ship size is higher than or equal the number of hit cells.


The following is an animated gif image for one random game using the previously mentioned algorithm:



Key words:

Battleship board game

Battleship game best strategy

Battleship game strategies

Battleship game probability function

Battleship game probability matrix

Battleship game number of combinations

Battleship game solver

Battleship game best efficiency

Battleship game patterns