VBA Error While Getting Index From Range Array
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm working with multiple range arrays. Each array has one column range. I definded my arrays as a Variant Array. And I arranged one spinbutton to textbox to get value of exact input from array range.
Array example:
...
fiber_oList = ThisWorkbook.Sheets("sarfArray").Range("B2:B18").Value2
mySpinButton.Max = UBound(fiber_oList, 1) - LBound(fiber_oList, 1) + 1
mySpinButton.Min = LBound(fiber_oList, 1)
Kal.Value = fiber_oList(mySpinButton.Value, 1)
...
and I have a function to get Index number of specific value from array.
Public Function whichIndex(araArray As Variant, vBul As Variant) As Integer
'
'
Dim i As Integer
If IsArray(araArray) Then
For i = 1 To UBound(araArray, 1)
If araArray(i, 1) = vBul Then
whichIndex = i
Exit Function
End If
Next i
whichIndex = Null
Else
MsgBox "Bu bir array değil."
End If
End Function
and then I used result of my function as below:
temp = whichIndex(fiber_oList, tekKal)
mySpinButton.Value = temp
Code is working correctly for first array. But for second array it gives an error:
function gives result of IsArray function FALSE.
and VBA gives
RUN TIME ERROR '380': Could not set the Value property. Invalid property value.
What can I do for this issue?
arrays excel vba excel-vba
asked Nov 16 '18 at 13:59
v.3v.3
53
|
show 5 more comments
I'm working with multiple range arrays. Each array has one column range. I definded my arrays as a Variant Array. And I arranged one spinbutton to textbox to get value of exact input from array range.
Array example:
...
fiber_oList = ThisWorkbook.Sheets("sarfArray").Range("B2:B18").Value2
mySpinButton.Max = UBound(fiber_oList, 1) - LBound(fiber_oList, 1) + 1
mySpinButton.Min = LBound(fiber_oList, 1)
Kal.Value = fiber_oList(mySpinButton.Value, 1)
...
and I have a function to get Index number of specific value from array.
Public Function whichIndex(araArray As Variant, vBul As Variant) As Integer
'
'
Dim i As Integer
If IsArray(araArray) Then
For i = 1 To UBound(araArray, 1)
If araArray(i, 1) = vBul Then
whichIndex = i
Exit Function
End If
Next i
whichIndex = Null
Else
MsgBox "Bu bir array değil."
End If
End Function
and then I used result of my function as below:
temp = whichIndex(fiber_oList, tekKal)
mySpinButton.Value = temp
Code is working correctly for first array. But for second array it gives an error:
function gives result of IsArray function FALSE.
and VBA gives
RUN TIME ERROR '380': Could not set the Value property. Invalid property value.
What can I do for this issue?
arrays excel vba excel-vba
asked Nov 16 '18 at 13:59
v.3v.3
53
You are returningNullfor non-arrays. Either return something else thatmySpinButton.Valuecan accept, or do not set it totempwhentempisNull?
– GSerg
Nov 16 '18 at 14:02
But all arrays have same length, and all indexes of arrays are filled with a specific value. Why it is returning NULL?
– v.3
Nov 16 '18 at 14:19
You said it yourself thatIsArrayis returningfalse. So that would not be an array.
– GSerg
Nov 16 '18 at 14:23
Either I made my arrays copy of first array as trial. The code is working first array, but for the remaining function returns false for IsArray. I cannot understand what is the cause. All things are similar for each array, and code seems ok.
– v.3
Nov 16 '18 at 14:37
1
There's no real problem with the code you posted, so the issue is likely with what you didn't post, which is how you declared and populated your second array. Exactly how did you copy your first array?
– Tim Williams
Nov 17 '18 at 8:01
|
show 5 more comments
I'm working with multiple range arrays. Each array has one column range. I definded my arrays as a Variant Array. And I arranged one spinbutton to textbox to get value of exact input from array range.
Array example:
...
fiber_oList = ThisWorkbook.Sheets("sarfArray").Range("B2:B18").Value2
mySpinButton.Max = UBound(fiber_oList, 1) - LBound(fiber_oList, 1) + 1
mySpinButton.Min = LBound(fiber_oList, 1)
Kal.Value = fiber_oList(mySpinButton.Value, 1)
...
and I have a function to get Index number of specific value from array.
Public Function whichIndex(araArray As Variant, vBul As Variant) As Integer
'
'
Dim i As Integer
If IsArray(araArray) Then
For i = 1 To UBound(araArray, 1)
If araArray(i, 1) = vBul Then
whichIndex = i
Exit Function
End If
Next i
whichIndex = Null
Else
MsgBox "Bu bir array değil."
End If
End Function
and then I used result of my function as below:
temp = whichIndex(fiber_oList, tekKal)
mySpinButton.Value = temp
Code is working correctly for first array. But for second array it gives an error:
function gives result of IsArray function FALSE.
and VBA gives
RUN TIME ERROR '380': Could not set the Value property. Invalid property value.
What can I do for this issue?
arrays excel vba excel-vba
asked Nov 16 '18 at 13:59
v.3v.3
53
I'm working with multiple range arrays. Each array has one column range. I definded my arrays as a Variant Array. And I arranged one spinbutton to textbox to get value of exact input from array range.
Array example:
...
fiber_oList = ThisWorkbook.Sheets("sarfArray").Range("B2:B18").Value2
mySpinButton.Max = UBound(fiber_oList, 1) - LBound(fiber_oList, 1) + 1
mySpinButton.Min = LBound(fiber_oList, 1)
Kal.Value = fiber_oList(mySpinButton.Value, 1)
...
and I have a function to get Index number of specific value from array.
Public Function whichIndex(araArray As Variant, vBul As Variant) As Integer
'
'
Dim i As Integer
If IsArray(araArray) Then
For i = 1 To UBound(araArray, 1)
If araArray(i, 1) = vBul Then
whichIndex = i
Exit Function
End If
Next i
whichIndex = Null
Else
MsgBox "Bu bir array değil."
End If
End Function
and then I used result of my function as below:
temp = whichIndex(fiber_oList, tekKal)
mySpinButton.Value = temp
Code is working correctly for first array. But for second array it gives an error:
function gives result of IsArray function FALSE.
and VBA gives
RUN TIME ERROR '380': Could not set the Value property. Invalid property value.
What can I do for this issue?
arrays excel vba excel-vba
arrays excel vba excel-vba
asked Nov 16 '18 at 13:59
v.3v.3
53
asked Nov 16 '18 at 13:59
v.3v.3
53
edited Nov 17 '18 at 7:15
v.3
asked Nov 16 '18 at 13:59
v.3v.3
53
asked Nov 16 '18 at 13:59
v.3v.3
53
asked Nov 16 '18 at 13:59
v.3v.3
53
53
You are returningNullfor non-arrays. Either return something else thatmySpinButton.Valuecan accept, or do not set it totempwhentempisNull?
– GSerg
Nov 16 '18 at 14:02
But all arrays have same length, and all indexes of arrays are filled with a specific value. Why it is returning NULL?
– v.3
Nov 16 '18 at 14:19
You said it yourself thatIsArrayis returningfalse. So that would not be an array.
– GSerg
Nov 16 '18 at 14:23
Either I made my arrays copy of first array as trial. The code is working first array, but for the remaining function returns false for IsArray. I cannot understand what is the cause. All things are similar for each array, and code seems ok.
– v.3
Nov 16 '18 at 14:37
1
There's no real problem with the code you posted, so the issue is likely with what you didn't post, which is how you declared and populated your second array. Exactly how did you copy your first array?
– Tim Williams
Nov 17 '18 at 8:01
|
show 5 more comments
You are returningNullfor non-arrays. Either return something else thatmySpinButton.Valuecan accept, or do not set it totempwhentempisNull?
– GSerg
Nov 16 '18 at 14:02
But all arrays have same length, and all indexes of arrays are filled with a specific value. Why it is returning NULL?
– v.3
Nov 16 '18 at 14:19
You said it yourself thatIsArrayis returningfalse. So that would not be an array.
– GSerg
Nov 16 '18 at 14:23
Either I made my arrays copy of first array as trial. The code is working first array, but for the remaining function returns false for IsArray. I cannot understand what is the cause. All things are similar for each array, and code seems ok.
– v.3
Nov 16 '18 at 14:37
1
There's no real problem with the code you posted, so the issue is likely with what you didn't post, which is how you declared and populated your second array. Exactly how did you copy your first array?
– Tim Williams
Nov 17 '18 at 8:01
You are returning
Null for non-arrays. Either return something else that mySpinButton.Value can accept, or do not set it to temp when temp is Null?– GSerg
Nov 16 '18 at 14:02
You are returning
Null for non-arrays. Either return something else that mySpinButton.Value can accept, or do not set it to temp when temp is Null?– GSerg
Nov 16 '18 at 14:02
But all arrays have same length, and all indexes of arrays are filled with a specific value. Why it is returning NULL?
– v.3
Nov 16 '18 at 14:19
But all arrays have same length, and all indexes of arrays are filled with a specific value. Why it is returning NULL?
– v.3
Nov 16 '18 at 14:19
You said it yourself that
IsArray is returning false. So that would not be an array.– GSerg
Nov 16 '18 at 14:23
You said it yourself that
IsArray is returning false. So that would not be an array.– GSerg
Nov 16 '18 at 14:23
Either I made my arrays copy of first array as trial. The code is working first array, but for the remaining function returns false for IsArray. I cannot understand what is the cause. All things are similar for each array, and code seems ok.
– v.3
Nov 16 '18 at 14:37
Either I made my arrays copy of first array as trial. The code is working first array, but for the remaining function returns false for IsArray. I cannot understand what is the cause. All things are similar for each array, and code seems ok.
– v.3
Nov 16 '18 at 14:37
1
1
There's no real problem with the code you posted, so the issue is likely with what you didn't post, which is how you declared and populated your second array. Exactly how did you copy your first array?
– Tim Williams
Nov 17 '18 at 8:01
There's no real problem with the code you posted, so the issue is likely with what you didn't post, which is how you declared and populated your second array. Exactly how did you copy your first array?
– Tim Williams
Nov 17 '18 at 8:01
|
show 5 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53339312%2fvba-error-while-getting-index-from-range-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53339312%2fvba-error-while-getting-index-from-range-array%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You are returning
Nullfor non-arrays. Either return something else thatmySpinButton.Valuecan accept, or do not set it totempwhentempisNull?– GSerg
Nov 16 '18 at 14:02
But all arrays have same length, and all indexes of arrays are filled with a specific value. Why it is returning NULL?
– v.3
Nov 16 '18 at 14:19
You said it yourself that
IsArrayis returningfalse. So that would not be an array.– GSerg
Nov 16 '18 at 14:23
Either I made my arrays copy of first array as trial. The code is working first array, but for the remaining function returns false for IsArray. I cannot understand what is the cause. All things are similar for each array, and code seems ok.
– v.3
Nov 16 '18 at 14:37
1
There's no real problem with the code you posted, so the issue is likely with what you didn't post, which is how you declared and populated your second array. Exactly how did you copy your first array?
– Tim Williams
Nov 17 '18 at 8:01