如何在VBA中创建和调用函数?

Question

我试图在VBA中创建我的第一个函数或过程。我在代码中使用的基本类型:

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

我现在拥有的不是一个函数，而是一个过程:

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

但最好是返回变量'传入布尔值'

我的代码是这样开始的(缩短版):

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

现在要放在函数中的代码:

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

则函数中应选用colRanges和SH。
Error I get is:

Byref参数类型不匹配

我做错了什么，如何正确地修复它?

Answer 1

你的问题格式有点乱，这使得它很难阅读所以如果你能更新它，我可以更精确但调用一个函数是这样的:

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

必须确保传递给函数的变量类型与函数中声明的类型相同(例如传递"hi";string"这是可以的，但如果var在函数中是long类型，这将无法工作。

在函数的末尾，你可以使用函数名=>"Test2 =您要发送回的函数的输出"