Results 1 to 22 of 22

Thread: Difficulty adding quotes around a variable in an excel formula

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    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!

  2. #2
    Fanatic Member
    Join Date
    Feb 2000
    Location
    Dunmow,Essex,England
    Posts
    898

    Re: Difficulty adding quotes around a variable in an excel formula

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

  3. #3

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

    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??

  4. #4
    PowerPoster
    Join Date
    Aug 2011
    Location
    B.C., Canada
    Posts
    2,887

    Re: Difficulty adding quotes around a variable in an excel formula

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

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

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

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

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

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

    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.

  8. #8

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

    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

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

    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

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

    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

  11. #11
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

    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

  12. #12

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

    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?

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

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

  14. #14

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

    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?

  15. #15
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

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

  16. #16

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

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

  17. #17
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

    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.

  18. #18
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: Difficulty adding quotes around a variable in an excel formula

    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
    Last edited by AussieJim; Feb 27th, 2012 at 04:45 PM.

  19. #19

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    9

    Re: Difficulty adding quotes around a variable in an excel formula

    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?

  20. #20
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: Difficulty adding quotes around a variable in an excel formula

    What type of value is in G1?

  21. #21
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Difficulty adding quotes around a variable in an excel formula

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

  22. #22
    New Member
    Join Date
    Feb 2012
    Posts
    15

    Re: Difficulty adding quotes around a variable in an excel formula

    TEXT() in excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width