Databases
StackOverflow2013
Postsdbo
POWhy would VBA TypeOf operator fail
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy would VBA TypeOf operator fail
    primarykey
    data
    text
    <p>I have been fighting with an Excel 2007 problem for several days now. Below is a listing of all facts I can think of that might be relevant:</p> <ol> <li><p><em>IDetailSheet</em> is a class declared in the VBA project with several methods, and which throws an error in its Class Initializer so that it cannot be instantiated (making it abstract).</p></li> <li><p><em>Option Explicit</em> is set in all modules.</p></li> <li><p>Ten worksheets in the VBA project <em>implement</em> <em>IDetailSheet</em> and compile cleanly (as does the entire project).</p></li> <li><p><em>CDetailSheets</em> is a class declared in the VBA project that wraps a Collection object and exposes the Collection object as a Collection of <em>IDetailSheet</em>. It also exposes some additional methods to perform certain methods of <em>IDetailSheet</em> on all collection menmbers.</p></li> <li><p>In its Class initializer (called from the <em>Workbook</em>_ <em>Open</em> event handler and assigned to a global variable), <em>CDetailSheet</em> executes the following code to populate the private collection <em>DetailSheets</em>: </p> <pre><code>Dim sht as EXCEL.WorkSheet For Each sht in ActiveWorkbook.Worksheets If TypeOf sht is IDetailSheet Then Dim DetailSheet as IDetailSheet Set DetailSheet = sht DetailSheets.Add DetailSheet, DetailSheet.Name End If Next sht </code></pre></li> <li><p>In certain Ribbon call-backs the following code is run: </p> <pre><code> If TypeOf ActiveWorkbook.ActiveSheet is IDetailSheet Then Dim DetailSheet as IDetailSheet Set DetailSheet = ActiveWorkbook.ActiveSheet DetailSheet.Refresh *[correction]* End If </code></pre></li> <li><p>All ActiveX controls have been removed from the Workbook, after having been identified with other stability issues (There were a few dozen originally). A Fluent Interface Ribbon has been created to replace the functionality originally associated with the ActiveX controls.</p></li> <li><p>There is a Hyperion add-in from the corporate template, but it is not used in this workbook.</p></li> </ol> <p>When all is said and done, the following symptom occurs when the workbook is run:</p> <ul> <li>Any number of instances of IDetailSheet are recognized in the CDetailSheets Initializer by <strong>TypeOf Is</strong>, from 1 (most common) to occasionally 2 or 3. Never zero, never more than 3, and most certainly never the full 10 available. (Not always the same one, though being near the front of the set seems to increase likelihood of being recognized.)</li> <li>Whichever instances of IDetailSheet implementation are discovered in the CDetailSheets initializer (and as near as I can determine, only such instances) are also recognized by <strong>TypeOf ... Is</strong> in the Ribbon call-back.</li> </ul> <p>Can anyone explain why most of the <strong>TypeOf ... Is</strong> operations are failing? Or how to fix the issue?</p> <p>I have resorted to manually creating v-tables (i.e. big ugly <em>Select Case ... End Select</em> statements) to get the functionality working, but I actually find it rather embarrassing to have my name beside such code. Besides which, I can see that being a future maintenance nightmare.</p> <p>Thinking that it might be a stale p-code issues, I went to the extent of deleting the Project.Bin file from the expanded XLSM zip, and then manually importing all the VBA code back in. No change. I also tried adding the project name to all the usages of <em>IDetailSheet</em> to make them <em>miFab.IDetailSheet</em>, but again to no avail. (<em>miFab</em> is the project name.)</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USVogel612
    1. USPieter Geerkens
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy would VBA TypeOf operator fail
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhy would VBA TypeOf operator fail
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhy would VBA TypeOf operator fail
      1. USd-stroyer
      1. VTFavorite
    1. COAre `Foreach` and `EndIf` typos in your question?
      singulars
      1. POWhy would VBA TypeOf operator fail
      1. USDoug Glancy
    2. CONo; but the indentation wont' work for me today. The If ... EndIf is nested inside the ForEach ... Next statement. However, the code is being typed from memory without Excel being handy for syntax checking.
      singulars
      1. POWhy would VBA TypeOf operator fail
      1. USPieter Geerkens
    3. COYes, but in VBA it would be `For Each` and `End If`.
      singulars
      1. POWhy would VBA TypeOf operator fail
      1. USDoug Glancy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload