This discussion is archived
2 Replies Latest reply: Dec 12, 2012 12:38 PM by Cdeblue RSS

Smartview VBA HypGetSheetOption problem

Cdeblue Newbie
Currently Being Moderated
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, windows 7, US

Edited by: Cdeblue on Dec 10, 2012 8:47 AM
  • 1. Re: Smartview VBA HypGetSheetOption problem
    TimG Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.



  • Correct Answers - 10 points
  • Helpful Answers - 5 points