2 Replies Latest reply: Dec 12, 2012 2:38 PM by Cdeblue RSS

    Smartview VBA HypGetSheetOption problem

    Cdeblue
      I am having an issue where my call to HypGetsheetOption #13 is returning an indiscypherable result. My #Missing label is set to #Missing but the call to retreive this returns garbage.

      using vba's len function, I see that I have 1 less character then what was entered and is visible on the Hyperion-Options tab.
      when I write the returned string to a cell on a worksheet, it looks like chinese.
      when i use strconv vbUnicode the returned value appears as "# M i s s i n" spaces between characters and the "g" chopped off.

      I don't see anything posted on anything like this, so I am suspecting I am overlooking something obvious.

      I am using R 11.1.1.3, windows 7, US

      Edited by: Cdeblue on Dec 10, 2012 8:47 AM
        • 1. Re: Smartview VBA HypGetSheetOption problem
          TimG
          There definitely seems to be something funky going on with the return from that function. I'm on 11.1.2, Win 7 64-bit / Office 2010 32-bit.

          I'm running the following code (the "A4" line is because sometimes, converting to Unicode then back again seemed to produce the correct result) and I don't get the same result every time. Sometimes it works. Declaring the return type as string rather than variant doesn't make any difference.

          Reluctant to say 'bug' because I can't even get consistent results, but unless I'm misunderstanding something in the VBA it does look like it.
          Sub TestMissing()
           
           Dim vtMissing As Variant
          
           vtMissing = HypGetSheetOption(ActiveWorkbook.ActiveSheet, 13)
           Range("A1", "A4").Clear
           Range("A1") = vtMissing
           Range("A2") = StrConv(vtMissing, vbUnicode)
           Range("A3") = StrConv(vtMissing, vbFromUnicode)
           Range("A4") = StrConv(StrConv(vtMissing, vbUnicode), vbFromUnicode)
          
          End Sub
          • 2. Re: Smartview VBA HypGetSheetOption problem
            Cdeblue
            Hey Tim,

            Thanks. Yes I had tried that myself including the use of string vs variant. It's weird you get good results sometimes. I've always gotten bad stuff back.

            I also tried seting the value to Missing instead of #Missing in Hyperion-Options thinking maybe the # was causing issues but that didn't seem to matter. In the back of my mind I am thinking maybe the answer may be in knowing exactly the codeset the string is being saved as. I looked but couldn't find anything on that.
            BTW, it appears to be all strings acting the same way.

            I appreciate your looking. I will continue and if I find the solution, I'll post it.

            Regards,
            Larry