Excel VBA Custom Function giving "Reference Not Valid" Error

Question

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

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. — Las Ten
– Las Ten, Commented Aug 28, 2012 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
– user1630640, Commented Aug 28, 2012 at 15:37
Can you provide what's in the source cells from Workbooks(this).Sheets(1).Cells(...)? — Joseph
– Joseph, Commented Aug 28, 2012 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
– user1630640, Commented Aug 28, 2012 at 16:00
@user1630640 just to be clear, the reference error is in the cell or from VBA? — Joseph
– Joseph, Commented Aug 28, 2012 at 16:26

gaz · Accepted Answer · 2016-11-29 10:40:24Z

2

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, 2016 at 10:40

gaz

213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tobriand · Accepted Answer · 2012-11-04 23:35:16Z

0

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, 2012 at 23:35

tobriand

1,19715 silver badges30 bronze badges

Collectives™ on Stack Overflow

Excel VBA Custom Function giving "Reference Not Valid" Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related