mgp
Mechanical
- May 30, 2001
- 224
I made a User defined function which returns the position of "Value" in an index, using the Excel function Match:
Public Function MyFunction(Value)
Dim vaNumber As Integer
Dim rgLookupRange As Range
Set rgLookupRange = ThisWorkbook.Worksheets("Sheet1"
.Range("A1:A3"
vaNumber = Application.Match(Value, rgLookupRange, 0)
MyFunction = vaNumber
End Function
Then I have a User Form with a combo box (cbIndex) and an OK button (bnOK)
The RowSource property of the combo box is range A1:A3 on Sheet1 which contains values and numbers, e.g.
A1=3, A2=a ,A3=1
Private Sub bnOK_Click()
Dim vaIndex As Variant
Dim vaReturnValue As Variant
vaIndex = cbIndex.Value
vaReturnValue = MyFunction(vaIndex)
With Worksheets("Sheet1"
.Range("c2" = vaReturnValue
End With
End Sub
When I run the form I get a Run Type Error (13) Type Mismatch depending on the format of the cells:
1) All cells are formatted as "standard", I then type in the values (3,a,1). When I run the code I get the error message if 3 or 1 is selected with the combobox
2) The cells are reformatted to "text", without changing the values. The error message is the same.
3) The values are re-entered in the text-formatted cells, and it all works.
4) The cells are reformatted back to "Standard". It still works.
5) The values are re-entered in the standard-formatted cells, the error is back.
When pasted into the worksheet, the function always works, irrespective of the format in the range.
Why does excel's Match function behave differently in VBA, and how can I make sure that my function input is always treated as text (as this is apparently required)?
A bit long but also a bit tricky. Any advice is appreciated.
Mogens
mgp@kabelnettet.dk
Public Function MyFunction(Value)
Dim vaNumber As Integer
Dim rgLookupRange As Range
Set rgLookupRange = ThisWorkbook.Worksheets("Sheet1"
.Range("A1:A3"vaNumber = Application.Match(Value, rgLookupRange, 0)
MyFunction = vaNumber
End Function
Then I have a User Form with a combo box (cbIndex) and an OK button (bnOK)
The RowSource property of the combo box is range A1:A3 on Sheet1 which contains values and numbers, e.g.
A1=3, A2=a ,A3=1
Private Sub bnOK_Click()
Dim vaIndex As Variant
Dim vaReturnValue As Variant
vaIndex = cbIndex.Value
vaReturnValue = MyFunction(vaIndex)
With Worksheets("Sheet1"
.Range("c2"
= vaReturnValueEnd With
End Sub
When I run the form I get a Run Type Error (13) Type Mismatch depending on the format of the cells:
1) All cells are formatted as "standard", I then type in the values (3,a,1). When I run the code I get the error message if 3 or 1 is selected with the combobox
2) The cells are reformatted to "text", without changing the values. The error message is the same.
3) The values are re-entered in the text-formatted cells, and it all works.
4) The cells are reformatted back to "Standard". It still works.
5) The values are re-entered in the standard-formatted cells, the error is back.
When pasted into the worksheet, the function always works, irrespective of the format in the range.
Why does excel's Match function behave differently in VBA, and how can I make sure that my function input is always treated as text (as this is apparently required)?
A bit long but also a bit tricky. Any advice is appreciated.
Mogens
mgp@kabelnettet.dk
