Convert month from numeric to alphabet

Here is how to convert month from number to text. For example, from 07 to July..

• We have to use date() function to help this conversion.
• In cell A1, cell value = 7
• In cell A2, type this function "=DATE(2012,A1,1)" -> this is for change text in cell A1 to date data type by adding day 1th and year 2012
• For convertion, we will use text() function to change month to format which we want
• In cell A3, type this function "=TEXT(A2, "mmm")" -> to change to Jul
• Display in cell A3 will be "Jul"
• If we need "JUL", type upper() function before text() in cell A3 -> "=UPPER(TEXT(A2, "mmm"))"
• We can change format instead of "mmm" to any which we want such as
• "mmm." -> this will show the abbrevation of month name with dot ex. "Jul."
• "mmmm" -> this will show full format of much ex. "July"
• "mmmmm" -> this will show the first alphabet of month ex. "J"

Convert Number to Text

Here are how to convert the existing number to text format in a cell.

1. At the cell, select Format Cells -> tab Number -> select Category = Text
2. At the other cell, type formula =TEXT(X,"0") -> X is refer to the cell which we want to change format, "0" = format text. After that copy this cell and then paste value to the original cell.
3. At the cell, select Data -> Text to Columns -> In the Wizard select Original data type = Delimited -> Next -> Wizard step 2 uncheck all Delimiters -> Next -> Wizard step 3 set Column data format = Text -> Finish

Excel Add-in (.xla, *.xlam)

To distribute the Excel function or procedure to user by keep it with Microsoft Excel program without opening the Excel file with function. Excel Add-in is a good choice, first, we have to convert our function to add-in and then install it in Microsoft Excel.

1. To create the Excel Add-in, this is simply save your workbook with macro in it and then save the Workbook as an Add-In (*.xla or *.xlam) in any path.

2. You can add a command to call the function from the ribbon by adding this code in the Workbook (Workbook_AddInInstall and Workbook_AddinUninstall), then save your Add-in file.

 
Option Explicit

Private Sub Workbook_AddInInstall()
    Dim cmbBar As CommandBar
    Dim cmbControl As CommandBarControl
     
    On Error Resume Next 'Just in case
    'Delete any existing menu item that may have been left.
    Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete 'Test is name of control to show on ribbon
     
    Set cmbBar = Application.CommandBars("Worksheet Menu Bar")
    Set cmbControl = cmbBar.Controls.Add(Type:=msoControlPopup) 'adds a menu item to the Menu Bar
    With cmbControl
        .Caption = "Test" 'names the menu item
        With .Controls.Add(Type:=msoControlButton) 'adds a dropdown button to the menu item
            .Caption = "Test Excel Add-in 1" 'adds a description to the menu item
            .OnAction = "module1.Test1" 'runs the specified macro
            .FaceId = 59 'assigns an icon to the dropdown
        End With
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Test Excel Add-in 2"
            .OnAction = "Module2.Test2"
            .FaceId = 64
        End With
    End With
    
    On Error GoTo 0
End Sub

Private Sub Workbook_AddinUninstall()
    
On Error Resume Next 'In case it has already gone.
    Application.CommandBars("Worksheet Menu Bar").Controls("Test").Delete
    On Error GoTo 0
End Sub

3. Install this Add-in to Microsoft Excel by follow this instruction
   3.1 Go to Excel Options > Add-Ins to open the Add-Ins dialog
   3.2 In this dialog, go to Manage Excel Add-ins > click Go...
   3.3 Browse to your Add-in from item 2 > OK
   3.4 Back to Add-Ins dialog > OK
   3.5 Excel Add-in is already installed to Microsoft Excel with the command bar to run macros in this Add-in

Register DLL and Reg file with Excel VBA

Here is a function to register DLL or OCX components

 
Public Sub RegisterComponent(sFilename As String, Optional bUnRegister As Boolean = False, Optional bHideResults As Boolean = True)
    If Len(Dir$(sFilename)) = 0 Then
        'File is missing
        MsgBox "Unable to locate file "" & sFileName & """, vbCritical
    Else
        If bUnRegister Then
            'Unregister a component
            If bHideResults Then
                'Hide results
                Shell "regsvr32 /s /u " & """" & sFilename & """"
            Else
                'Show results
                Shell "regsvr32 /u " & """" & sFilename & """"
            End If
        Else
            'Register a component
            If bHideResults Then
                'Hide results
                Shell "regsvr32 /s " & """" & sFilename & """"
            Else
                'Show results
                Shell "regsvr32 " & """" & sFilename & """"
            End If
        End If
    End If
End Sub

For example :

 
Sub test()
    RegisterComponent ("c:\test.dll")
End Sub

Here is a function to export a section of registry to .reg file

Public Sub RegeditExport(sKey As String, sFilename As String)
    Shell "regedit.exe /s /e " & Chr(34) & sFilename & Chr(34) & " " & Chr(34) & sKey & Chr(34), vbHide
End Sub

Here is a function to import windows registry file (.reg file) to the registry

 
Public Sub RegeditImport(sFilename As String)
    Shell "regedit.exe /s /c " & Chr(34) & sFilename & Chr(34), vbHide
End Sub

For example:

 
Sub test()
    RegeditImport "C:\test.reg"
End Sub

Get or set computer name function

Here is a function to get or set the computer name

 
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpbuffer As String, nsize As Long) As Long
Private Declare Function SetComputerName Lib "kernel32" Alias "SetComputerNameA" (ByVal lpComputerName As String) As Long

'Purpose     :  Returns the name of the local machine/computer
'Inputs      :  N/A
'Outputs     :  Returns the name of the local machine
'Notes       :  Uses a private static for speed


Public Function MachineName() As String
    Dim lRet As Long
    Dim lMaxLen As Long
    Static ssMachineName As String
    
    If Len(ssMachineName) = 0 Then
        lMaxLen = 100
        ssMachineName = String$(lMaxLen, vbNullChar)
        lRet = GetComputerName(ssMachineName, lMaxLen)
        ssMachineName = Left$(ssMachineName, lMaxLen)
    End If
    MachineName = ssMachineName
End Function

'Purpose     :  Sets the name of the local machine/computer
'Inputs      :  sName           The new name of the local machine/computer
'Outputs     :  Returns True if failed to change local machine name


Function MachineNameSet(sName As String) As Boolean
    MachineNameSet = IIf(SetComputerName(sName & vbNullChar) = 0, False, True)
End Function

Credit : http://www.visualbasic.happycodings.com/Applications-VBA/code23.html

Read ini value

Function for read a value of each key from ini file.

 
Public Function ReadIniValue(ByVal INIpath As String, ByVal KEY As String, ByVal Variable As String) As String  
 Dim NF As Integer  
 Dim temp As String  
 Dim LcaseTemp As String  
 Dim ReadyToRead As Boolean  
 AssignVariables:  
     NF = FreeFile  
     ReadIniValue = ""  
     KEY = "[" & LCase$(KEY) & "]"  
     Variable = LCase$(Variable)  
 EnsureFileExists:  
   If Dir(INIpath) = "" Then  
    MsgBox " No file you are tring to read ", vbOKOnly, "Error"  
    Exit Function  
   End If  
   'Open INIpath For Binary As NF  
   ' Close NF  
   'SetAttr INIpath, vbArchive  
 LoadFile:  
   Open INIpath For Input As NF  
   While Not EOF(NF)  
      Line Input #NF, temp  
      LcaseTemp = LCase$(Trim(temp))  
      If InStr(LcaseTemp, "[") <> 0 Then ReadyToRead = False  
      If LcaseTemp = KEY Then ReadyToRead = True  
      If InStr(LcaseTemp, "[") = 0 And ReadyToRead = True Then  
       If InStr(LcaseTemp, Variable & "=") = 1 Then  
         ReadIniValue = Mid$(temp, 1 + Len(Variable & "="))  
         Close NF: Exit Function  
         End If  
      End If  
   Wend  
   Close NF  
 End Function  

Split function

Split function is used for separating any string to substring by using a specified delimiter.

Syntax
Split(expression[, delimiter[, limit[, compare]]])
For example:
Public Function SplitText(str, n, delim)
'Returns the nth element from a string,
'using a specified separator character
Dim x As Variant    x = Split(str, delim)
    If n > 0 And n - 1 <= UBound(x) Then
       SplitText= x(n - 1)
    Else       SplitText= ""
    End If
End Function

Example: Range("A1") = test1,test2,test3

Sub test()
Dim result1 As String
Dim result2 As String
Dim result3 As String   
   result1 = SplitText(Range("A1").Value, 1, ",") 'result = test1
   result2 = SplitText(Range("A1").Value, 2, ",") 'result = test2
   result3 = SplitText(Range("A1").Value, 3, ",") 'result = test3
End Sub