How to create "Data Validation" with a list in other worksheet ?
Go to menu "Insert" -> "Name" -> "Define",
add one new define name "listPIC", with "Refer to" field set to "=WorkSheetList!$J2:$J10"
How to create "Data Validation" with a dynamic list ?
=OFFSET(WorkSheetList!$J$1,0,0,COUNTA(WorkSheetList!$J:$J),1)
How to convert a formula from A1 style to R1C1 style, and vice-versa ?
strFormula = Application.ConvertFormula(strFormula, xlA1, xlR1C1)
strFormula = Application.ConvertFormula(strFormula, xlR1C1, xlA1)
How to find last used row and column ?
intLastUsedRow = Worksheets("Sheet1").Range("D65536").End(xlUp).Row
intLastUsedRow = wksTemp.UsedRange.Rows(wksTemp.UsedRange.Rows.Count).Row
intLastUsedRow = wksMain.Cells(wksMain.UsedRange.Rows.Count+1, 2).End(xlUp).Row
intLastUsedCol = Worksheets("Sheet1").Range("B2").End(xlToRight).Column
How to protects the worksheet, but still allows changes via macro code ?
ActiveSheet.Protect UserInterfaceOnly:=True
How to format one byte hex number, with auto leading 0 ?
strTmp = Trim("0x" & Replace(Format(Hex(bytHexSub), String(2, "@")), " ", "0"))
Or
strTmp = "0x" & Replace(Format(Hex(bytHexSub), "@@"), " ", "0")
How to convert decimal into hex string, vice versa?
strTmp = CStr(Hex(10)) ' Convert 10 into 0x0A
value = CInt("&H" & strTmp ) 'Converts back &HA to 10
How to worksheet function?
rowUsed = WorksheetFunction.Max(7, rowUsed)
How to retrieve worksheet name?
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
How to reverse lsb msb?
Public Function bit_reverse8(i As Integer) As Integer
Dim ix As Integer
Dim iy As Integer
Dim n As Integer
iy = 0
For n = 0 To 7
iy = 2 * iy
iy = iy + (i And 1)
i = i \ 2
Next n
bit_reverse8 = iy
End Function