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

EmpDeity

New Member
Joined
Mar 9, 2009
Messages
13
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
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
 
Upvote 0
Alas, that did not solve the problem. and I did double check that the function get_gap_k is returing a "single"
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.

Code:
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**

[TABLE="width: 240"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Tag[/TD]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[TD="width: 64, bgcolor: transparent"]Conductivity [/TD]
[TD="width: 64, bgcolor: transparent"]Derating[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]n[/TD]
[TD="bgcolor: transparent"]air[/TD]
[TD="bgcolor: transparent, align: right"]0.0007[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]0.008[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]s[/TD]
[TD="bgcolor: transparent"]solder[/TD]
[TD="bgcolor: transparent, align: right"]1.27[/TD]
[TD="bgcolor: transparent, align: right"]0.5[/TD]
[TD="bgcolor: transparent, align: right"]0.004[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]l[/TD]
[TD="bgcolor: transparent"]lipstick[/TD]
[TD="bgcolor: transparent, align: right"]0.013[/TD]
[TD="bgcolor: transparent, align: right"]0.9[/TD]
[TD="bgcolor: transparent, align: right"]0.008[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited by a moderator:
Upvote 0
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:

Code:
Application.Volatile True

to the top of the function and then run the code?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top