VBA-Programmierung in Microsoft Excel

Tutorial: Excel-Beispiele

Kopfzeile der Zelle mit dem höchsten Wert ermitteln.

Gruppe

UDF

Bereich

Zeichenfolge

Thema

Kopfzeile der Zelle mit dem höchsten Wert ermitteln.

Problem

Wie ermittle ich in einer Matrix die Kopfzeile der Zelle, in der sich der höchste Wert der Matrix befindet?

Lösung

Folgende Matrixformel: =INDEX(A1:E11;1;SUMME(WENN(A1:E11=MAX(A1:E11); SPALTE(A1:E11))))




ClassModule: frmCaption

Private Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
End Type

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
                ByVal lpClassName As String, _
                ByVal lpWindowName As String) As Long

Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
              (ByVal hWnd As Long, ByVal nIndex As Long) As Long
                
Private Declare Function SetWindowLong _
               Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, _
               ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

Private Declare Function DrawMenuBar Lib "user32" ( _
               ByVal hWnd As Long) As Long

Private Sub cmdCancel_Click()
  Unload Me
End Sub

Private Sub cmdOn_Click()
  Call fncHasUserformCaption(True)
End Sub

Private Sub cmdOff_Click()
   Call fncHasUserformCaption(False)
End Sub

Private Sub UserForm_Initialize()
  Call fncHasUserformCaption(False)
End Sub

Private Function fncHasUserformCaption(bState As Boolean)
  Dim Userform_hWnd As Long
  Dim Userform_Style As Long
  Dim Userform_Rect As RECT
  Const GWL_STYLE = (-16)
  Const WS_CAPTION = &HC00000
  Userform_hWnd = FindWindow( _
     lpClassName:=IIf(Val(Application.Version) > 8, _
     "ThunderDFrame", "ThunderXFrame"), _
     lpWindowName:=Me.Caption)
  Userform_Style = GetWindowLong(hWnd:=Userform_hWnd, _
     nIndex:=GWL_STYLE)
  If bState = True Then
     Userform_Style = Userform_Style Or WS_CAPTION
  Else
     Userform_Style = Userform_Style And Not WS_CAPTION
  End If
  Call SetWindowLong(hWnd:=Userform_hWnd, nIndex:=GWL_STYLE, _
     dwNewLong:=Userform_Style)
  Call DrawMenuBar(hWnd:=Userform_hWnd)
End Function

StandardModule: Modul1

Sub DialogAufruf()
   frmCaption.Show
End Sub

    


Beiträge aus dem Excel-Forum zu den Themen UDF und Zeichenfolge