Difficulty adding quotes around a variable in an excel formula

I have a data retrieval function, which works through an excel plug in. For Example, call it ItemValue. This Function is supposed to retrieve data based on the alphanumeric ID number, entered as a string in the first argument, and the data type entered into the second argument.(Such as "Value", "Units", etc.)

When creating the formula, it uses a value from an entry box in a form I created, variable name IDnum1. When running the formula without quotes over this variable as below, I get no errors.

Cells(1, 2).Formula = "=ItemValue(" & IDnum1.Value & ", ""Units"")"

(say for instance IDnum1.Value = Item25)

Output being -> =ItemValue(Item25, "Units")

However i need Quotes over the "Item25" for the function to work properly. From my understanding, I just need to add double quotes on each side of the variable for quotes to appear. However, when I do this, typed as shown below. I get a Type Mismatch Error.

Cells(1, 2).Formula = "=ItemValue(""" & IDnum1.Value & """, ""Units"")"

When Debugging that string.
For ex: Debug.Print "=ItemValue(""" & IDnum1.Value & """, ""Units"")"
I get the output that I am looking for.

Does anyone know why I am getting this error?
Any help would be greatly appreciated.

Thanks in advance!

try using chr$(34) where you want to place quote's instead of the literal.

that resulted in an "Application-defined or object defined error"

I also even tried to take my quotes out of the formula and adding them to the string in my input box. This is also throwing a type mismatch error. Why would excel not like that if I addes quotes to my actual string that i entered??

vb Code:

Cells(1, 2).Formula = "=ItemValue(" & Chr(34) & IDnum1.Value & Chr(34) ,Chr(34) & "Units" & Chr(34))"

Could be that CHR$() is not defined in VBA try CHR() without the $ as suggested above.

I attempted running it with CHR(34) without the $ and still ran into a type mismatch exception. Any other ideas?

Well if you get the result you want when you do a debug.print maybe you should create another string variable. Assign your string to the new variable then set your formula to the new variable and see if you still get the error.

I do not know if that would help, for one I do not code in Excel and 2 the type mismatch indicates that you are using an incorrect datatype somewhere.

I assume the error points to the line you have shown us?


Edit:

You could also try manually typing in the formula as you would have it to be generated and see if Excel allows it.

Yes, the error is pointing to the line of code in question. Also, I have previously attempted to assign the string to a variable and have still received the type mismatch. This type mismatch error occurs when I assign my temporary string to the cell.

Dim temp As String
temp = "=ItemValue(" & Chr(34) & IDnum1.Value & Chr(34) & ", ""eng.units"")"
Cells(1, 2).Formula = temp 'Error thrown here

Add a debug.print temp on the next line after it is assigned.
If the output in the debug windows looks correct copy it to the clipboard
Go into your excel sheet and try pasting it in as your formula and see what happens. If it works then we have a mystery. If it doesn't then you may get a better idea of what you will need to change.

If I had to guess here I would guess that the first parameter is expecting a numeric value where you are trying to pass a string

The debug line prints the formula as I want it formatted and when i refresh my functions it retrieved the value as im expecting.

Ive previously had it refer to a cell with the ID value in it (entered manually). When I try to refer to it this way I still get an error as well.

Cells(1, 7) = IDnum1.Value
temp = "=ItemValue(G1, ""eng.units"")"
Debug.Print temp
Cells(1, 2).Formula = temp 'Still throws type mismatch on this line

Ok we do not seem to be gettign anywhere here.

Is there a function in your code called ItemValue()?

If so could you paste it here using code tags of course or at least post the line that defines the function

I actually dont have access to the code for the ItemValue() function. It is supplied in my company's add-in in excel. I figured out a way to get it to work though. Instead of retrieving my item name with IDnum1.value, i used IDnum1.text instead and it started to work. Any idea how that would have been causing the problem?

Perhaps the value property is numeric whereas the text is a string

Im actually still having issues with this. Sometimes it wants to work, sometimes it doesn't (most times it doesnt). I can be encurring a type mismatch error, however, if i turn off my add-in(that the ItemValue function works through), then my formulas are entered into their cells just fine. Is there any way to force excel not to check the argument types of an add in? I can't look at the source code for ItemValue, however, the output I am producing is correct. This whole situation is really puzzling to me. Any work-around ideas to this?

Do you have the intelisense popup when you write the code for it that shows what data types the ItemValue function is expecting?

No, that currently isnt showing up. Do you have to enable that in some way for plug in functions?

I really don't know just thought it may help answer the question. Maybe someone else can chime in who actually uses VBA in Excel.

Just a thought

Is the value of cell G1 in excel the type expected by ItemValue function?


Edit
What I mean is, make sure that there some value in G1 which suits the data expected by G1

I was able to get access to the source code for the plug in!!!

First off, yes, the contents of the cell G1 is a string which is what is expected by ItemValue. Here is the header of the ItemValue Function:

Function ItemValue(ID As String, Field As String) As Variant

The first line in the function is "Application.Volatile"

Could this line be what is causing a problem? Im not exactly sure what Volatile changes in the sense of calling the function.

Anyways, Aside from that, both the arguments requested are strings and I believe i have been passing both strings as arguments.

What confuses me is that i got it to work earlier today and after a couple of runs it started giving me the type mismatch errors again(on lines in which i had changed no code).

Any thoughts?

What type of value is in G1?

So the return from the function is a variant maybe that is the issue maybe you should try surrounding the function call with CStr()

TEXT() in excel