Option Explicit
Sub test()
Debug.Print TransposeChord("A", 3)
End Sub
Private Function TransposeChord(InputChord As String, HalfStep As Long) As String
Static chords As Collection
Dim i As Long
Dim chordArr As Variant
Dim isMinor As Boolean
If LCase(InputChord) = InputChord Then isMinor = True
chordArr = Split("C,C#,D,D#,E,F,F#,G,G#,A,A#,H", ",")
If chords Is Nothing Then
Set chords = New Collection
For i = LBound(chordArr) To UBound(chordArr)
chords.Add i, UCase(chordArr(i))
Next i
End If
TransposeChord = chordArr((chords.Item(UCase(InputChord)) + HalfStep) Mod 12)
If isMinor Then TransposeChord = LCase(TransposeChord)
End Function