Using VBA to create a formula in a cell giving me "#VALUE!"

[EmpDeity]

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.

 

[Dryver14]

have you tried worksheet.calculate before the end of the sub

 

[EmpDeity]

Yes, I tried

Worksheets("NFG").Calculate

at the end of the sub. No changes.

 

[Dryver14]

The worksheet must be interpreting your input as text which is why when you press F2 the enter it updates itself, I am not sure how to fix this

 

[BartH]

Hi,

When the output of a function is interpreted as text, a trick to transform it to a value is to multiply it by 1.

So if "= module3.get_gap_k(R2)" gives you #VALUE !, you might try "= module3.get_gap_k(R2)*1". When "= module3.get_gap_k(R2)" produces anything that can be interpreted as a value, *1 will do the trick.

This trick also works for formulas that result in a cell that behaves as a text formatted cell (result aligned left). Add *1 to the formula and it will be transformed into a value.

Grtz BartH

 

[EmpDeity]

Alas, that did not solve the problem. and I did double check that the function get_gap_k is returing a "single"

 

[RoryA]

My bet would be that your UDF is incorrectly written, so I suggest you post it. Also, you're better off returning a Double from a UDF since that is how cell values are stored.

 

[EmpDeity]

UDF = User Defined Function?

Fortunately, it is short. See below. I use the function throughout the spreadsheet in several places and it is returning the correct value (when the VBA macros call it). A few sample lines of data that are read in are also appended.

Public Function get_gap_k(g_flag As String) As Single
Dim i, j As Integer
Do While Worksheets("Gap Materials").Cells(i + 2, 1) <> ""
    i = i + 1
    Module3.G_Tag(i) = Trim(LCase(Worksheets("Gap Materials").Cells(i + 1, 1)))
    Module3.G_K(i) = Trim(LCase(Worksheets("Gap Materials").Cells(i + 1, 3)))
    Module3.G_Derate(i) = Trim(LCase(Worksheets("Gap Materials").Cells(i + 1, 4)))
Loop
For j = 1 To i
    If LCase(Trim(g_flag)) = LCase(Trim(Module3.G_Tag(j))) Then
        get_gap_k = Module3.G_K(j) * Module3.G_Derate(j)
        Exit Function
    End If
Next j
get_gap_k = 999
End Function

**Sample Data**

Tag	Name	Conductivity	Derating
n	air	0.0007	1	0.008
s	solder	1.27	0.5	0.004
l	lipstick	0.013	0.9	0.008

<tbody>
</tbody>

 

[RoryA]

I'd have to assume, absent the workbook for proper debugging, that something is causing an error in the UDF (yes, User Defined Function ) when your macro runs and since it isn't volatile (which it really should be given that it is dependent on cells that aren't passed to it as arguments). What happens if you add the line:

Application.Volatile True

to the top of the function and then run the code?

 

[EmpDeity]

This seems to have solved the problem. It is now working. I got it working for a line that writes the text that calls just the get_mat_k function. and then for the larger, full equation.

I'll google Application.Volatile True to see what is really happening.

We can all go home now and relax.

Many, many thanks.