Skip to content

Instantly share code, notes, and snippets.

Avatar

Rich Pollock RichPollock

View GitHub Profile
@RichPollock
RichPollock / my.cnf locations.sh
Last active Dec 23, 2018
Show my.cnf locations mysqld is configured to read from
View my.cnf locations.sh
mysqld --verbose --help | grep -A 1 "Default options"
@RichPollock
RichPollock / gist:ea30219979e2fd4125dd8e0418a14bfa
Created Aug 18, 2016
Creates named ranges to each cell using the content of the cell as the name
View gist:ea30219979e2fd4125dd8e0418a14bfa
Sub NameCellRangesAfterCellContent()
Dim columnNumber As Integer
columnNumber = 2
For i = 1 To 100
If Sheets("Sheet Name").Range("A" & i).Value <> "" Then
ActiveWorkbook.Names.Add Name:=Sheets("Sheet Name").Range("A" & i).Value, RefersToR1C1:="='Sheet Name'!R" & i & "C" & columnNumber
End If
Next i
End Sub
@RichPollock
RichPollock / gist:c91953671d1be506e817aa75cc905853
Created May 16, 2016
Update all hyperlinked shapes to reflect a new sheet name
View gist:c91953671d1be506e817aa75cc905853
Sub ChangeHyperlinkedShapesToUseNewSheetNames()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each shp In ws.Shapes
If shp.Type = 1 Then
On Error Resume Next
If shp.Hyperlink.SubAddress = "'Old sheet'!A1" Then
shp.Hyperlink.SubAddress = "'New sheet'!A1"
End If
End If
@RichPollock
RichPollock / gist:04091c29789dc6e5c55c
Created Oct 13, 2015
VBA function for Cholesky decomposition
View gist:04091c29789dc6e5c55c
Function CholeskyDecompose(matrix As Range)
Dim A, LTM() As Double, S As Double
Dim j As Long, K As Long, i As Long, N As Long, M As Long
A = matrix
N = matrix.Rows.Count
M = matrix.Columns.Count
If N <> M Then
@RichPollock
RichPollock / gist:fd2a4e6cc4a8b5f5f888
Last active Aug 29, 2015
Generate a hyperlinked table of contents linking to each sheet in an Excel workbook (blog post: http://blog.richpollock.com/2014/11/generating-a-hyperlinked-table-of-contents-for-an-excel-workbook-using-vba/)
View gist:fd2a4e6cc4a8b5f5f888
Sub GenerateLinkedTOCFromWorkSheetNames()
Dim ProposedTOCWorksheetName As String
Dim NewTOCWorksheetName As String
Dim CurrentWorksheet As Worksheet
Dim Count As Integer
ProposedTOCWorksheetName = "TOC"
NewTOCWorksheetName = "TOC"
RowCounter = 2
@RichPollock
RichPollock / gist:5f15a1bbc76b65a907cb
Last active Feb 1, 2019
Change Excel column widths by column number
View gist:5f15a1bbc76b65a907cb
' For example: Call SetColumnWidths("Sheet1", Array(2, 4, 6, 8), 0) would collapse columns B, D, F and H on Sheet1
Sub SetColumnWidths(sheet As String, columnNumbers As Variant, width As Integer)
For i = 0 To UBound(columnNumbers)
Worksheets(sheet).Cells(1, columnNumbers(i)).EntireColumn.ColumnWidth = width
Next i
End Sub
' Or to hide a contiguous range:
Sub HideColumns(sheet As String, leftmostCol As Integer, rightmostCol As Integer)
@RichPollock
RichPollock / gist:4738253
Created Feb 8, 2013
Triangular distribution function in VBA. Usage: =TRIDIST(RAND(), 1, 10, 5)
View gist:4738253
Function TRIDIST(random As Double, min As Double, max As Double, mode As Double)
If mode < min Or max < mode Then
TRIDIST = CVErr(xlErrValue)
Else
If random <= (mode - min) / (max - min) Then
TRIDIST = min + Sqr((max - min) * (mode - min) * random)
Else
TRIDIST = max - Sqr((max - min) * (max - mode) * (1 - random))
End If
End If
@RichPollock
RichPollock / gist:4609965
Last active Dec 11, 2015
Set the tool tips of all hyperlinked shapes in Excel to the text contained in the shape (thereby replacing the default tool tip, which includes the document file path)
View gist:4609965
Sub AddScreenTipsToHyperlinkedShapesUsingShapeText()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each shp In ws.Shapes
If shp.Type = 1 Then
On Error Resume Next
shp.Hyperlink.ScreenTip = shp.OLEFormat.Object.ShapeRange.TextFrame.Characters.Text
End If
Next shp
Next ws
@RichPollock
RichPollock / gist:4609894
Created Jan 23, 2013
Change the data range of an Excel chart without using .Activate
View gist:4609894
With Sheets("Source Data Sheet Name")
Set ChartXRange = .Range("A1:A10")
Set ChartRange = .Range("B1:B10")
End With
Sheets("Chart Sheet Name").ChartObjects("Chart Name").Chart.SeriesCollection(1).XValues = ChartXRange
Sheets("Chart Sheet Name").ChartObjects("Chart Name").Chart.SeriesCollection(1).Values = ChartRange
@RichPollock
RichPollock / gist:4609859
Created Jan 23, 2013
Remove leading and trailing whitespace from selected cells in Excel
View gist:4609859
Sub StripWhiteSpaceFromSelection()
Dim SelectedRange As Range
Set SelectedRange = Selection
For Each Col In SelectedRange.Columns
For Each Row In SelectedRange.Rows
Cells(Row.Row, Col.Column).Value = Trim(Cells(Row.Row, Col.Column).Value)
Next Row
Next Col
End Sub
You can’t perform that action at this time.