Excel VBA Custom Function giving “Reference Not Valid” Error
I've attempted to track down and solve this error for hours now and I just can't figure it out. Here's the setup.
I have one excel workbook which has two sheets in it: "Input" and "Calculations". I've written in a few custom functions to calculate certain things. These functions are used in the "Calculations" sheet, but reference cells in the "Input" sheet. Now if I'm just using the sheet itself everything works perfectly fine and the functions work.
However, I have a second excel workbook which interacts with the first. I have a macro in the second workbook which attempts to define values in the "Input" sheet of the first workbook. However, when it does this, suddenly the functions don't work. When I attempt to trace the error of the cell in the "Calculations" sheet, and attempt to go to the cell in "Input" sheet, it claims the reference is invalid.
I have no idea what the problem is. At first I thought it maybe had something to do with the name of the first workbook (which was "Log K Calculator 7.0.0.xlsm") but I've tried changing that and I get the same problem. Here is the macro in the second sheet which attempts to change the values in the first:
Sub macro()
Dim logK As String
Dim this As String
logK = "Log K Calculator 7.0.0.xlsm"
this = ThisWorkbook.Name
Workbooks(logK).Activate
Workbooks(logK).Sheets("Input").Cells(11, 4).Value = Workbooks(this).Sheets(1).Cells(1, "B").Value
Workbooks(logK).Sheets("Input").Cells(12, 4).Value = Workbooks(this).Sheets(1).Cells(2, "B").Value
Workbooks(logK).Sheets("Input").Cells(14, 4).Value = Workbooks(this).Sheets(1).Cells(3, "B").Value
End Sub
excel vba reference
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
|
show 4 more comments
I've attempted to track down and solve this error for hours now and I just can't figure it out. Here's the setup.
I have one excel workbook which has two sheets in it: "Input" and "Calculations". I've written in a few custom functions to calculate certain things. These functions are used in the "Calculations" sheet, but reference cells in the "Input" sheet. Now if I'm just using the sheet itself everything works perfectly fine and the functions work.
However, I have a second excel workbook which interacts with the first. I have a macro in the second workbook which attempts to define values in the "Input" sheet of the first workbook. However, when it does this, suddenly the functions don't work. When I attempt to trace the error of the cell in the "Calculations" sheet, and attempt to go to the cell in "Input" sheet, it claims the reference is invalid.
I have no idea what the problem is. At first I thought it maybe had something to do with the name of the first workbook (which was "Log K Calculator 7.0.0.xlsm") but I've tried changing that and I get the same problem. Here is the macro in the second sheet which attempts to change the values in the first:
Sub macro()
Dim logK As String
Dim this As String
logK = "Log K Calculator 7.0.0.xlsm"
this = ThisWorkbook.Name
Workbooks(logK).Activate
Workbooks(logK).Sheets("Input").Cells(11, 4).Value = Workbooks(this).Sheets(1).Cells(1, "B").Value
Workbooks(logK).Sheets("Input").Cells(12, 4).Value = Workbooks(this).Sheets(1).Cells(2, "B").Value
Workbooks(logK).Sheets("Input").Cells(14, 4).Value = Workbooks(this).Sheets(1).Cells(3, "B").Value
End Sub
excel vba reference
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
1
This works perfectly for me with the WB names changed to Book1 and Book2 ... Judged by the error message that complains about "reference" I guess there's something around formulas in the cells. So you're modifying a cell that is referenced somewhere else by a formula.
– mshthn
Aug 28 '12 at 15:26
The frustrating thing about this problem is that it isn't consistent. Sometimes it happens and sometimes it doesn't and I'm having a real trouble pinning down the cause of it.
– user1630640
Aug 28 '12 at 15:37
Can you provide what's in the source cells from Workbooks(this).Sheets(1).Cells(...)?
– Joseph
Aug 28 '12 at 15:56
Just numbers. It doesn't seem to care what numbers I use but one such combination is: 10000, 300, 600. The weird issue is that when I step through the code line by line, it doesn't even call the function in the first worksheet. Starting in the macro, it runs down until it changes the first value in the logK worksheet. Normally this would cause the function in "Calculations" to reevaluate, which should bring it up as I step through the lines, but it never even brings it up or throws an error. It just jumps over that part and continues on, and when I check the cell, it gives an error there.
– user1630640
Aug 28 '12 at 16:00
@user1630640 just to be clear, the reference error is in the cell or from VBA?
– Joseph
Aug 28 '12 at 16:26
|
show 4 more comments
I've attempted to track down and solve this error for hours now and I just can't figure it out. Here's the setup.
I have one excel workbook which has two sheets in it: "Input" and "Calculations". I've written in a few custom functions to calculate certain things. These functions are used in the "Calculations" sheet, but reference cells in the "Input" sheet. Now if I'm just using the sheet itself everything works perfectly fine and the functions work.
However, I have a second excel workbook which interacts with the first. I have a macro in the second workbook which attempts to define values in the "Input" sheet of the first workbook. However, when it does this, suddenly the functions don't work. When I attempt to trace the error of the cell in the "Calculations" sheet, and attempt to go to the cell in "Input" sheet, it claims the reference is invalid.
I have no idea what the problem is. At first I thought it maybe had something to do with the name of the first workbook (which was "Log K Calculator 7.0.0.xlsm") but I've tried changing that and I get the same problem. Here is the macro in the second sheet which attempts to change the values in the first:
Sub macro()
Dim logK As String
Dim this As String
logK = "Log K Calculator 7.0.0.xlsm"
this = ThisWorkbook.Name
Workbooks(logK).Activate
Workbooks(logK).Sheets("Input").Cells(11, 4).Value = Workbooks(this).Sheets(1).Cells(1, "B").Value
Workbooks(logK).Sheets("Input").Cells(12, 4).Value = Workbooks(this).Sheets(1).Cells(2, "B").Value
Workbooks(logK).Sheets("Input").Cells(14, 4).Value = Workbooks(this).Sheets(1).Cells(3, "B").Value
End Sub
excel vba reference
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
I've attempted to track down and solve this error for hours now and I just can't figure it out. Here's the setup.
I have one excel workbook which has two sheets in it: "Input" and "Calculations". I've written in a few custom functions to calculate certain things. These functions are used in the "Calculations" sheet, but reference cells in the "Input" sheet. Now if I'm just using the sheet itself everything works perfectly fine and the functions work.
However, I have a second excel workbook which interacts with the first. I have a macro in the second workbook which attempts to define values in the "Input" sheet of the first workbook. However, when it does this, suddenly the functions don't work. When I attempt to trace the error of the cell in the "Calculations" sheet, and attempt to go to the cell in "Input" sheet, it claims the reference is invalid.
I have no idea what the problem is. At first I thought it maybe had something to do with the name of the first workbook (which was "Log K Calculator 7.0.0.xlsm") but I've tried changing that and I get the same problem. Here is the macro in the second sheet which attempts to change the values in the first:
Sub macro()
Dim logK As String
Dim this As String
logK = "Log K Calculator 7.0.0.xlsm"
this = ThisWorkbook.Name
Workbooks(logK).Activate
Workbooks(logK).Sheets("Input").Cells(11, 4).Value = Workbooks(this).Sheets(1).Cells(1, "B").Value
Workbooks(logK).Sheets("Input").Cells(12, 4).Value = Workbooks(this).Sheets(1).Cells(2, "B").Value
Workbooks(logK).Sheets("Input").Cells(14, 4).Value = Workbooks(this).Sheets(1).Cells(3, "B").Value
End Sub
excel vba reference
excel vba reference
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
edited Sep 11 '15 at 3:41
pnuts
48.7k76299
48.7k76299
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
asked Aug 28 '12 at 14:55
user1630640user1630640
1814
1814
1
This works perfectly for me with the WB names changed to Book1 and Book2 ... Judged by the error message that complains about "reference" I guess there's something around formulas in the cells. So you're modifying a cell that is referenced somewhere else by a formula.
– mshthn
Aug 28 '12 at 15:26
The frustrating thing about this problem is that it isn't consistent. Sometimes it happens and sometimes it doesn't and I'm having a real trouble pinning down the cause of it.
– user1630640
Aug 28 '12 at 15:37
Can you provide what's in the source cells from Workbooks(this).Sheets(1).Cells(...)?
– Joseph
Aug 28 '12 at 15:56
Just numbers. It doesn't seem to care what numbers I use but one such combination is: 10000, 300, 600. The weird issue is that when I step through the code line by line, it doesn't even call the function in the first worksheet. Starting in the macro, it runs down until it changes the first value in the logK worksheet. Normally this would cause the function in "Calculations" to reevaluate, which should bring it up as I step through the lines, but it never even brings it up or throws an error. It just jumps over that part and continues on, and when I check the cell, it gives an error there.
– user1630640
Aug 28 '12 at 16:00
@user1630640 just to be clear, the reference error is in the cell or from VBA?
– Joseph
Aug 28 '12 at 16:26
|
show 4 more comments
1
This works perfectly for me with the WB names changed to Book1 and Book2 ... Judged by the error message that complains about "reference" I guess there's something around formulas in the cells. So you're modifying a cell that is referenced somewhere else by a formula.
– mshthn
Aug 28 '12 at 15:26
The frustrating thing about this problem is that it isn't consistent. Sometimes it happens and sometimes it doesn't and I'm having a real trouble pinning down the cause of it.
– user1630640
Aug 28 '12 at 15:37
Can you provide what's in the source cells from Workbooks(this).Sheets(1).Cells(...)?
– Joseph
Aug 28 '12 at 15:56
Just numbers. It doesn't seem to care what numbers I use but one such combination is: 10000, 300, 600. The weird issue is that when I step through the code line by line, it doesn't even call the function in the first worksheet. Starting in the macro, it runs down until it changes the first value in the logK worksheet. Normally this would cause the function in "Calculations" to reevaluate, which should bring it up as I step through the lines, but it never even brings it up or throws an error. It just jumps over that part and continues on, and when I check the cell, it gives an error there.
– user1630640
Aug 28 '12 at 16:00
@user1630640 just to be clear, the reference error is in the cell or from VBA?
– Joseph
Aug 28 '12 at 16:26
1
1
This works perfectly for me with the WB names changed to Book1 and Book2 ... Judged by the error message that complains about "reference" I guess there's something around formulas in the cells. So you're modifying a cell that is referenced somewhere else by a formula.
– mshthn
Aug 28 '12 at 15:26
This works perfectly for me with the WB names changed to Book1 and Book2 ... Judged by the error message that complains about "reference" I guess there's something around formulas in the cells. So you're modifying a cell that is referenced somewhere else by a formula.
– mshthn
Aug 28 '12 at 15:26
The frustrating thing about this problem is that it isn't consistent. Sometimes it happens and sometimes it doesn't and I'm having a real trouble pinning down the cause of it.
– user1630640
Aug 28 '12 at 15:37
The frustrating thing about this problem is that it isn't consistent. Sometimes it happens and sometimes it doesn't and I'm having a real trouble pinning down the cause of it.
– user1630640
Aug 28 '12 at 15:37
Can you provide what's in the source cells from Workbooks(this).Sheets(1).Cells(...)?
– Joseph
Aug 28 '12 at 15:56
Can you provide what's in the source cells from Workbooks(this).Sheets(1).Cells(...)?
– Joseph
Aug 28 '12 at 15:56
Just numbers. It doesn't seem to care what numbers I use but one such combination is: 10000, 300, 600. The weird issue is that when I step through the code line by line, it doesn't even call the function in the first worksheet. Starting in the macro, it runs down until it changes the first value in the logK worksheet. Normally this would cause the function in "Calculations" to reevaluate, which should bring it up as I step through the lines, but it never even brings it up or throws an error. It just jumps over that part and continues on, and when I check the cell, it gives an error there.
– user1630640
Aug 28 '12 at 16:00
Just numbers. It doesn't seem to care what numbers I use but one such combination is: 10000, 300, 600. The weird issue is that when I step through the code line by line, it doesn't even call the function in the first worksheet. Starting in the macro, it runs down until it changes the first value in the logK worksheet. Normally this would cause the function in "Calculations" to reevaluate, which should bring it up as I step through the lines, but it never even brings it up or throws an error. It just jumps over that part and continues on, and when I check the cell, it gives an error there.
– user1630640
Aug 28 '12 at 16:00
@user1630640 just to be clear, the reference error is in the cell or from VBA?
– Joseph
Aug 28 '12 at 16:26
@user1630640 just to be clear, the reference error is in the cell or from VBA?
– Joseph
Aug 28 '12 at 16:26
|
show 4 more comments
2 Answers
2
active
oldest
votes
You might want to check how Excel is treating whatever value is sitting in your external workbook. E.g. is it treating the numbers as text? You could check this by entering, say '10000, '300 and '600 manually in the input sheet (forcing it to treat them as text but display numbers), and see if you get the same ref error.
If you do, then casting the value into a typed variable (Long, Decimal, you name it) would probably fix the issue. Or at least make VBA choke on the incorrectly formatted ones (e.g. some have spaces in them, which might explain the apparent inconsistency)
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
add a comment |
I know this thread is 4 years old but, just in case anybody is still searching for the answer like I was, for me this error was caused not by my code but by the fact that I was running the procedure by clicking on a textbox which I had copied from another workbook. I had attached the procedure/macro to the textbox but had forgotten that there was already a hyperlink assigned to the same textbox. Removing the hyperlink fixed the issue.
answered Nov 29 '16 at 10:40
gazgaz
11
add a comment |
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%2f12162038%2fexcel-vba-custom-function-giving-reference-not-valid-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You might want to check how Excel is treating whatever value is sitting in your external workbook. E.g. is it treating the numbers as text? You could check this by entering, say '10000, '300 and '600 manually in the input sheet (forcing it to treat them as text but display numbers), and see if you get the same ref error.
If you do, then casting the value into a typed variable (Long, Decimal, you name it) would probably fix the issue. Or at least make VBA choke on the incorrectly formatted ones (e.g. some have spaces in them, which might explain the apparent inconsistency)
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
add a comment |
You might want to check how Excel is treating whatever value is sitting in your external workbook. E.g. is it treating the numbers as text? You could check this by entering, say '10000, '300 and '600 manually in the input sheet (forcing it to treat them as text but display numbers), and see if you get the same ref error.
If you do, then casting the value into a typed variable (Long, Decimal, you name it) would probably fix the issue. Or at least make VBA choke on the incorrectly formatted ones (e.g. some have spaces in them, which might explain the apparent inconsistency)
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
add a comment |
You might want to check how Excel is treating whatever value is sitting in your external workbook. E.g. is it treating the numbers as text? You could check this by entering, say '10000, '300 and '600 manually in the input sheet (forcing it to treat them as text but display numbers), and see if you get the same ref error.
If you do, then casting the value into a typed variable (Long, Decimal, you name it) would probably fix the issue. Or at least make VBA choke on the incorrectly formatted ones (e.g. some have spaces in them, which might explain the apparent inconsistency)
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
You might want to check how Excel is treating whatever value is sitting in your external workbook. E.g. is it treating the numbers as text? You could check this by entering, say '10000, '300 and '600 manually in the input sheet (forcing it to treat them as text but display numbers), and see if you get the same ref error.
If you do, then casting the value into a typed variable (Long, Decimal, you name it) would probably fix the issue. Or at least make VBA choke on the incorrectly formatted ones (e.g. some have spaces in them, which might explain the apparent inconsistency)
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
answered Nov 4 '12 at 23:35
tobriandtobriand
717719
717719
add a comment |
add a comment |
I know this thread is 4 years old but, just in case anybody is still searching for the answer like I was, for me this error was caused not by my code but by the fact that I was running the procedure by clicking on a textbox which I had copied from another workbook. I had attached the procedure/macro to the textbox but had forgotten that there was already a hyperlink assigned to the same textbox. Removing the hyperlink fixed the issue.
answered Nov 29 '16 at 10:40
gazgaz
11
add a comment |
I know this thread is 4 years old but, just in case anybody is still searching for the answer like I was, for me this error was caused not by my code but by the fact that I was running the procedure by clicking on a textbox which I had copied from another workbook. I had attached the procedure/macro to the textbox but had forgotten that there was already a hyperlink assigned to the same textbox. Removing the hyperlink fixed the issue.
answered Nov 29 '16 at 10:40
gazgaz
11
add a comment |
I know this thread is 4 years old but, just in case anybody is still searching for the answer like I was, for me this error was caused not by my code but by the fact that I was running the procedure by clicking on a textbox which I had copied from another workbook. I had attached the procedure/macro to the textbox but had forgotten that there was already a hyperlink assigned to the same textbox. Removing the hyperlink fixed the issue.
answered Nov 29 '16 at 10:40
gazgaz
11
I know this thread is 4 years old but, just in case anybody is still searching for the answer like I was, for me this error was caused not by my code but by the fact that I was running the procedure by clicking on a textbox which I had copied from another workbook. I had attached the procedure/macro to the textbox but had forgotten that there was already a hyperlink assigned to the same textbox. Removing the hyperlink fixed the issue.
answered Nov 29 '16 at 10:40
gazgaz
11
answered Nov 29 '16 at 10:40
gazgaz
11
answered Nov 29 '16 at 10:40
gazgaz
11
answered Nov 29 '16 at 10:40
gazgaz
11
11
add a comment |
add a comment |
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%2f12162038%2fexcel-vba-custom-function-giving-reference-not-valid-error%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
1
This works perfectly for me with the WB names changed to Book1 and Book2 ... Judged by the error message that complains about "reference" I guess there's something around formulas in the cells. So you're modifying a cell that is referenced somewhere else by a formula.
– mshthn
Aug 28 '12 at 15:26
The frustrating thing about this problem is that it isn't consistent. Sometimes it happens and sometimes it doesn't and I'm having a real trouble pinning down the cause of it.
– user1630640
Aug 28 '12 at 15:37
Can you provide what's in the source cells from Workbooks(this).Sheets(1).Cells(...)?
– Joseph
Aug 28 '12 at 15:56
Just numbers. It doesn't seem to care what numbers I use but one such combination is: 10000, 300, 600. The weird issue is that when I step through the code line by line, it doesn't even call the function in the first worksheet. Starting in the macro, it runs down until it changes the first value in the logK worksheet. Normally this would cause the function in "Calculations" to reevaluate, which should bring it up as I step through the lines, but it never even brings it up or throws an error. It just jumps over that part and continues on, and when I check the cell, it gives an error there.
– user1630640
Aug 28 '12 at 16:00
@user1630640 just to be clear, the reference error is in the cell or from VBA?
– Joseph
Aug 28 '12 at 16:26