Yahtzee in Excel
Posted on February 20, 2019Download this free Yahtzee game written entirely in Microsoft Excel.
It uses the little-known Excel Camera tool to show pictures of random dice.
Great waste of time.
Excel Default Formulas
Posted on February 20, 2019Developed as a Microsoft Excel add-in, Default Formulas gives the developer the control of protected mode without the limitations.
Establish default formulas for a range.
- Works great with Tables (ListObjects).
- Automatically uses structured references if available.
Operator can override default formulas.
- Default formulas and overriden formulas are designated to the operator by color.
- Makes changes to formulas obvious.
- Operator can return cells to their default formulas by simply pressing [Delete].
Use Defaulted and NotDefaulted functions to return ranges of cells for use in calculations.
- NotDefaulted function makes it simple for default formulas to refer to themselves without encountering circular reference errors.
Create default formulas with circular references.
- As long as any one of cells in the circular relationship are not defaulted, no errors will result.
- Very compact method for allowing operator to choose from multiple input methods.
- Examples:
- Establish default formulas for Start, Duration, and Finish. Operator can override their choice of any two and the remaining default formula computes the missing value.
- Establish default formulas for Qty, Unit Price, and Total Price. Operator can override their choice of any two and the remaining default formula computes the missing value.
- Establish default formulas for Fahrenheit and Celsius. Operator can override their choice and the remaining default formula computes the corresponding value.
Hotkey reveals DefaultFormulas user interface
Default Formulas are stored in hidden named ranges, which can be revealed and edited manually if desired.
Excel COLUMN and ROW functions
Posted on January 01, 2019Using the COLUMN and ROW functions are preferred over the CELL function, because the latter is volatile and will calculate with every change in Excel, even to cells that are not precedents. However, care must be taken to avoid accidentally passing a range of cells, instead of a single cell, to COLUMN and ROW.
If passed a single cell, it returns the column number of the cell.
If passed a range of cells, even if the range is an entire column or row, it returns an array of column or row numbers for each cell in the passed range. This is confusing, and very slow.
If a range is not passed to COLUMN or ROW the range will default to the current cell, which is usually perfect.
Even when a single cell is passed, it is still technically passed as a single-value array, and a single-value array is returned. Although Excel will generally still work as expected, to be explicit would be better, like INDEX(COLUMN(),1), which will return the first value from the array.
Passing Decimal Parameters to SQL Server
Posted on April 09, 2017' Execute stored procedure to add record.
Set cmd = ADOCommand("InsertLogWaypoint", cn, adCmdStoredProc)
With cmd
.Parameters.Append .CreateParameter("Time", adDBTimeStamp, adParamInput, , rstLog.Fields("Time").Value)
.Parameters.Append .CreateParameter("Lat", adSingle, adParamInput, , rstLog.Fields("Latitude").Value)
.Parameters.Append .CreateParameter("Long", adSingle, adParamInput, , rstLog.Fields("Longitude").Value)
.Parameters.Append .CreateParameter("Acc", adInteger, adParamInput, , rstLog.Fields("Accuracy").Value)
.Parameters.Append .CreateParameter("Dir", adInteger, adParamInput, , rstLog.Fields("Direction").Value)
.Parameters.Append .CreateParameter("Speed", adNumeric, adParamInput, , rstLog.Fields("Speed").Value)
With .Parameters("Speed")
.Precision = 18
.NumericScale = 9
End With
.Parameters.Append .CreateParameter("LocID", adInteger, adParamInput, , rstLog.Fields("LocationID").Value)
.Parameters.Append .CreateParameter("ID", adInteger, adParamOutput)
.Execute
End With