I've spent the better part of the afternnon googling around and looking for the solution, maybe I'm just not searching with the right words.
I've got an extensive macro enabled spreadsheet that helps us pre- and post-process our thermal analyses. Some of my results spreadsheets have live formulas in them, rather than just the calculated values. But now, one of my formulas is dependent on the function that I wrote that resides in the spreadsheet.
The following piece of code is looped on "j" to create several hundred rows of equations
ThetaBuf = "=1./(1./(Q" & Trim(Str(j + 1)) & "/module3.get_gap_k(r" & Trim(Str(j + 1)) & ")/(u" & Trim(Str(j + 1))
ThetaBuf = ThetaBuf & "/" & Trim(Str(MilScale)) & ")/(V" & Trim(Str(j + 1)) & "/" & Trim(Str(MilScale)) & ")) +1./(N"
ThetaBuf = ThetaBuf & Trim(Str(j + 1)) & "/O" & Trim(Str(j + 1)) & "/P" & Trim(Str(j + 1)) & "/M" & Trim(Str(j + 1))
ThetaBuf = ThetaBuf & "/L" & Trim(Str(j + 1)) & "))"
Worksheets("NFG").Cells(j + 1, 33).Formula = ThetaBuf
And as an example, here is what one cell looks like:
=1/(1/(Q2/module3.get_gap_k(R2)/(U2/1000)/(V2/1000)) +1/(N2/O2/P2/M2/L2))
So what is the issue? When I execute the macro that calls this portion of code, instead of a value (like 218.7) being in the cell, I get "#VALUE!"
***BUT*** if I double-click on the cell and hit return (I.E., do nothing) the expected value pops in (I.E., 218.7).
And some more information. My function "module2.get_gap_k(R2)" seems to be the problem child. If I remove the call to that function and enter in a typical value (the function looks up a thermal conductivity of a material referenced in cell R2), everything works fine. As a matter of fact, if I just use VBA to write out a cell that looks like "= module3.get_gap_k(R2)", I get the "#VALUE!" result.
So what is the secret handshake to get the cells to process the formula and function and give me the number (without me having to touch the cells)?
And I hope I described this properly. Many thanks in advance.
I've got an extensive macro enabled spreadsheet that helps us pre- and post-process our thermal analyses. Some of my results spreadsheets have live formulas in them, rather than just the calculated values. But now, one of my formulas is dependent on the function that I wrote that resides in the spreadsheet.
The following piece of code is looped on "j" to create several hundred rows of equations
ThetaBuf = "=1./(1./(Q" & Trim(Str(j + 1)) & "/module3.get_gap_k(r" & Trim(Str(j + 1)) & ")/(u" & Trim(Str(j + 1))
ThetaBuf = ThetaBuf & "/" & Trim(Str(MilScale)) & ")/(V" & Trim(Str(j + 1)) & "/" & Trim(Str(MilScale)) & ")) +1./(N"
ThetaBuf = ThetaBuf & Trim(Str(j + 1)) & "/O" & Trim(Str(j + 1)) & "/P" & Trim(Str(j + 1)) & "/M" & Trim(Str(j + 1))
ThetaBuf = ThetaBuf & "/L" & Trim(Str(j + 1)) & "))"
Worksheets("NFG").Cells(j + 1, 33).Formula = ThetaBuf
And as an example, here is what one cell looks like:
=1/(1/(Q2/module3.get_gap_k(R2)/(U2/1000)/(V2/1000)) +1/(N2/O2/P2/M2/L2))
So what is the issue? When I execute the macro that calls this portion of code, instead of a value (like 218.7) being in the cell, I get "#VALUE!"
***BUT*** if I double-click on the cell and hit return (I.E., do nothing) the expected value pops in (I.E., 218.7).
And some more information. My function "module2.get_gap_k(R2)" seems to be the problem child. If I remove the call to that function and enter in a typical value (the function looks up a thermal conductivity of a material referenced in cell R2), everything works fine. As a matter of fact, if I just use VBA to write out a cell that looks like "= module3.get_gap_k(R2)", I get the "#VALUE!" result.
So what is the secret handshake to get the cells to process the formula and function and give me the number (without me having to touch the cells)?
And I hope I described this properly. Many thanks in advance.