How to create and call a function in VBA?-CodePudding

I am trying to create my first function or procedure in VBA. Basic types which I use in the code:

Private Type T_DATA_COLUMN_INFO
   count As Integer
   positiveColumnsColors(2) As Long ' decimal values from Hex
   negativeColumnsColors(1) As Long
   excludeColumnsColors(1) As Long
   zeroTop As Integer ' position of zero, the Top property of zero rectangle
   dataWidth As Integer
   negativeDataHeight As Integer
   positiveDataFound As Boolean
   negativeDataFound As Boolean
End Type

' All is on horizontal axis except negativeValueY
Private Type T_COLUMN_RANGES
    Xmin As Integer ' (Left) actually
    Xmid As Integer ' middle position
    Xmax As Integer ' Left   Column width
    Xgap As Integer ' Gap between column rectangles
    Xpitch As Integer ' Gap between colRanges()(1).mid and colRanges()(2).mid
    negativeValueY As Integer  ' Top Height
    Q1Y As Integer
    Q2Y As Integer ' position of median
    Q3Y As Integer
    initiated As ENUM_INITIATED
End Type

What I have currently is not a function but procedure:

Sub SetColumnRanges(Sh As Shape, i As Integer)
    colRanges(0).Width = 0
End Sub

But best would be if it would return variable 'passed boolean'

My code starts like this (shorted version):

Sub LookForAxis()
    Dim colRanges() As T_COLUMN_RANGES
    Dim i As Integer
    Dim Sh As Shape
    Dim passed As Boolean
    
    ReDim colRanges(1 To 1) As T_COLUMN_RANGES
    colRanges(1).initiated = 0
    ...
    With ActiveWindow.Selection
      If (.Type = ppSelectionShapes) And (.ShapeRange.Type = msoGroup) Then
        For Each Sh In .ShapeRange.GroupItems
          If (Sh.Name Like "Rec*") Then
            For i = 1 To dataInfo.count
              If Not passed Then  ' If the array ...
                ' code skipped
                ' HERE I TRY TO CALL THE PROCEDURE OR FUNCTION... best if passed is returned for function:
                SetColumnRanges Sh, i

Now the code to be placed in the function:

If Sh.Fill.ForeColor.RGB = dataInfo.positiveColumnsColors(1) Then
    colRanges(i).initiated = colRanges(i).initiated Or columns_initiated_positive
    If colRanges(i).Q1Y = 0 Then
         colRanges(i).Q3Y = 0
         colRanges(i).Q2Y = Sh.Top 
         colRanges(i).Q1Y = (Sh.Top   Sh.Height) * -1
    ElseIf colRanges(i).Q1Y < 0 Then
         If colRanges(i).Q1Y * -1 < Sh.Top   Sh.Height Then
             tempInt = colRanges(i).Q2Y * -1
             colRanges(i).Q3Y = colRanges(i).Q2Y  ' Make the old value positive
             colRanges(i).Q2Y = tempInt  ' Make the old value positive
             colRanges(i).Q1Y = Sh.Top   Sh.Height
         Else
            ' Sh.Name = "Q3"
             colRanges(i).Q3Y = Sh.Top   Sh.Height
             colRanges(i).Q1Y = colRanges(i).Q1Y * -1  ' Make the old value positive
         End If
    End If
ElseIf Sh.Fill.ForeColor.RGB = dataInfo.negativeColumnsColors(1) Then
    ' Sh.Name = "Negative"
    colRanges(i).initiated = colRanges(i).initiated Or columns_initiated_negative
End If

So colRanges and SH should be used in the function.
Error I get is:

Byref argument type mismatch

What am I doing wrong and how to fix it correctly?

CodePudding user response：

you question format is a bit messed up which makes it complicated to read so if you can update it I could be more precise but calling a function works like this:

    Sub test()
    
        MsgBox test2("hi")
        
        Dim var As String
        var = test2("hi")
        MsgBox var
    
    End Sub
    
    Function test2(var As String) As Boolean
        
        test2 = True
    
    End Function

you must make sure the type of the vars you are passing to your function are of the same type as the ones declared in your function (e.g. passing "hi" to "string" is ok but this would not work if var would be of type long in the function.

at the end of your function you send the result back by using the function name => "Test2 = output of your function you want to send back".