Posts by Mr. Plow

    I am attempting to first delete a module in a workbook, then replace it with a module of the same name from another workbook. The following code is deleting the module, then replacing it with the desired code but in a module with same name and "1" added to the end.



    The DeleteModule code is:


    (Thanks to Chip Pearson for his code http://cpearson.com/excel/vbe.aspx.)

    Is it possible to have a help file that is not the Microsoft Developer Reference? I want to provide a file that gives users an idea of what updates they will be installing so I want the help file to be a Word document.


    I know I could do this by creating my own userform, but I would like to use standard MsgBox function if possible.


    here's my code:



    Running this and clicking the Help button brings up the standard Excel help menu. I have no idea what the context parameter should be in this either.


    Thanks.

    Re: VBA calling global array with parameters known to be out of range


    I'd still like to find a solution to this problem for possible other issues in the future, but I have a band-aid that will get me through my current purpose without any problems.


    The global array will now be filled for all possible combinations, but non-published ones will get a number that I know cannot be a real published interest rate. The code for filling my global array is



    Then I have my userform initialize code count the number of values <> 99 and create that many rows in the listbox, then populate the rows with information when glblVar <> 99



    Hope this helps. I'm still interested in knowing a way to avoid the error when calling for an array item when a specific element doesn't exist in the array.

    I am using historical interest rate information stored in a global variable array to populate a listbox for a userform. The historical data is somewhat asymetric, so that some months may have one or two published rates while others have several. But when I later call the global array to populate the listbox, I end up looking for an array element with parameters that are in range but have not had an element created.


    I define and populate the global array here:


    My global variables are getting populated by data maintained in a spreadsheet. The array parameters are month, year, law year. All month/year/law year combos present a unique set, but sometimes a month/year combo might have 2 different law years and other times it might have 3.


    This global array is then used in the following code to populate a listbox for a userform through the nested for/next loops:


    I need to have loopvar3 go from -1 to 1 for some month/year items, but only from 0 to 1 on others. And I want to allow my range to be years into the future so I don't have to update code when new rates are published each month. So my code bombs when trying to pull the glblVar item for some month/year/law year combos in my If statements and also in the varListData(rowcount,4) line.


    My ultimate goal is to have the listbox populated with only the information in my historical data spreadsheet.


    Thanks in advance.

    I am in the process of centalizing a bunch of functions into an addin. I have created a custom ribbon for the addin, and now am trying to create a button on the ribbon that will open an existing Word document. All this is being done with code that is resident within the addin.


    My first thought was to adapt some prior code that, when attached to a macro button in a macro-enabled spreadsheet, will open the spreadsheet just fine:



    I have the reference "Microsoft Word 12.0 Object Library" appropriately checked.


    However, using the same code in the addin and then calling it through the ribbon gives me this error message: "Wrong number of arguments or invalid property assignment".


    In a possibly related issue, I'm getting that same error message when I try to run a simple sub to open a userform. The userform is in the addin and so is this code, which is called by a button in the custom ribbon (also in the addin):


    Code
    Sub OpenHistInfoForm()
        frmHistInfo.Show
    End Sub


    Very simple code that seems like it should work easily. This is what would be done if opening the userform through a macro button on the spreadsheet.


    Thanks in advance.

    Re: Manual calculation for part of a sheet


    I'd really like to find a solution to this issue also. I think a solution might be finding something that operates like the opposite of the dirty method. In other words, when running the application.calculation process the workbook will ignore a section that is deemed "not dirty".


    I may have something that's better than copying hard numbers to a backup sheet. Although I think this will only be useful if you can determine a specific order of sheets to calculate. So depending on the setup of your spreadsheet, you might be able to do something like the following:

    Code
    Public Sub CustomCalc()
    Dim blnCalc As Boolean
    If blnCalc = True Then
        Application.Calculate
    Else
        Worksheets("Sheet1").Calculate
        Worksheets("Sheet2").Calculate
    End If
    End Sub


    If you know that you can calculate Sheet1 first and then Sheet2, this may work. But I think it may not work if you have things on Sheet1 that depend on Sheet2 and also things on Sheet 2 that depend on Sheet1. That's my problem.


    I have a tab, call it SheetSlow that has a ton of formulas and takes a long time to calculate, but it doesn't need to run everytime. So when i make a change in another portion of the spreadsheet i don't want SheetSlow to run solely because of the calculation time. But I won't be able to put an order of calculations on the other tabs because there is alot of back and forth (without any circular references of course). Is there a way that I can make the Application.Calculate command ignore SheetSlow and do the regular Excel processing on other tabs? I mentioned possibly the opposite of dirty, but i haven't found any way to do this.

    Re: Excel VBA to cycle through Word bookmarks and insert ranges (text, graphs &amp; table


    Running Excel 2007 and Word 2007. An Office 2010 upgrade is probably not too far off in the future so hopefully final code will be usable in both versions.


    I'm trying to import images into the bookmark, not charts. The reason is because I'm using my excel file for projections and other purposes after creating the Word reports. This will make the linked charts in Word change which I don't want. By first saving the Excel chart as a picture and then importing the picture into the bookmark, I don't have to worry about my Word report changing when my spreadsheet later does.

    Re: Excel VBA to cycle through Word bookmarks and insert ranges (text, graphs &amp; table


    I didn't have any success with the code above. [EDIT - See above] I have posted some updated and additional code in the original thread here, http://www.ozgrid.com/forum/sh…48798&p=572856#post572856. I too am trying to get to the bottom of this issue. Main issue still left is removing existing images in a bookmark before inserting the new image INTO the bookmark (as opposed to AFTER the bookmark).

    Re: Excel VBA to cycle through Word bookmarks and insert Excel range value


    [EDIT - moved from other thread]
    I know this discussion has jumped over to another thread so I'm going to link them together here: http://www.ozgrid.com/forum/showthread.php?t=157163&page=1.


    I have pulled all this code from many sources and can take personal credit for very little of it.


    The PopulateWordBookmarks sub has been improved to separate out bookmarks that are text and those that are charts/graphs. I've done this by beginning the names of my excel ranges that I want to be pictures with "chart". The process takes all the ranges beginning with "chart" and saves as a temporary .gif, then attempts to import that .gif file INTO the bookmark. Currently struggling with INTO and not AT.


    Here is relevant code (suppressing some of the duplicative stuff previously posted):



    The FillBookmark sub used for any ranges that do not begin with "chart" remains unchanged.


    The FillBookmarkImage sub is trying to delete all prior images and text from a bookmark, then replace with the new image. Right now it's inserting pictures AFTER the bookmark:



    Thanks for the help.

    Re: Excel VBA to cycle through Word bookmarks and insert Excel range value


    defining the variable in the first line of the sub should work. not sure why excel doesn't like that, but if you have a work-around then maybe you're ok.


    try this for the RangeNameExists sub:



    Hope that works for you

    Re: Excel VBA to cycle through Word bookmarks and insert Excel range value


    I searched all over for other advice and found some that has worked. My complete sub works with the following:


    using another sub.....


    I lost the link to the site that created this FillBookmark process, but I'm infinitely grateful. I wish I could give you the due recognition for your help.

    Re: Excel VBA to cycle through Word bookmarks and insert Excel range value


    Follow up question....

    When running this more than once on the same Word document it is inserting the text multiple times at the same bookmark. Is there a way to overwrite the text that had previously been added at the bookmark?

    My thought is to use Word fields, but I want to make sure the formatting from Excel gets carried over. And on the whole I absolutely hate the switches on mergefields.

    Thanks.

    I'm trying to create a process in an Excel addin to 1) open a Word document, 2) search the document for bookmarks, then 3) insert the value from an Excel range that is named the same as the Word bookmark.

    I'm hoping that I can get this to be done within Excel code and not any Word VBA. I also want this code to be pretty generic to be used with documents of varying numbers of bookmarks. So I would like to use variables and loops to the fullest extent possible.

    Here's what I have so far that isn't working for me:



    This is crashing on the .bookmarks.Item(n.Name) command, but I'm not certain the rest of the process works properly either.

    I have a library workbook info. I want a function in an addin to go to the library file and return a value. I don't want to use a macro for the whole thing because I want to maintain the formula of calling the function when its used in other spreadsheets. Is it possible to do this?

    I'm having trouble with the following code....all of which is in modules in my addin.




    There are various other posts on values from external files.

    Re: Refering To Cell In Closed Workbook


    Trying to get data by process roy describes. straightforward but i'm missing something obvious. please help identify what's wrong with this.



    Thanks in advance