Posts by wbsmith

  • Macro Code To Work Between 2 Open Workbooks

    Re: Setting Workbooks As Public Object Variables


    Yes, the two macros and the public declarations are in the same file--I think this is also same project.


    What is my code doing (what is intended): First Macro Prompts user to pick a data file, sets the object references, and pastes worksheet names and a couple other values from data file into calculations file. Second macro will eventually take a user selection made by control tool and paste the data from the appropriate worksheet of the data file into the calculation file.


    How is my code doing (are things working??): Hard to tell. I worked for IBM when they issued last version of lotus 1-2-3. It would go haywire and do things, even outside of macros, that were just plain wrong. I think that is what you mean by loss of state. In excel, I find it hard to know if problems are an Excel problem or that I am not tuned in to particulars of VBA. For instance below is code samples where I tried a variety of scenarios for selecting a range in a worksheet, and use the autocomplete to check myself. N


    [hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]sorry, I hit the wrong key and cut off the reply. In the code sample, None of the entries ever give ".range" as a valid autocomplete, but I am certain I have seen it as an autocomplete choice before. I don't know if Excel has "gone to lunch" or if I don't appreciate the very specific nuances of how Visual basic is organized. It sure doesn't seem as obvious as it is made to sound in books.


    Anyway, for the public object variable, thanks for your thoughts and I am willing to try any ideas--I had tried moving public variable declarations to new module based on another post...seemed to work in the first macro, but didn't carry over to when I tried to access in second macro. That's when I tried using "static."

  • Macro Code To Work Between 2 Open Workbooks

    I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here


    Code
    Set current_wb = ThisWorkbook
Dat_Fil = Application.GetOpenFilename
Workbooks.Open Dat_Fil
Set wbkFrom = Application.ActiveWorkbook


    NOTE-Workbook object variables are declared outside any macro, and as "public." Code here

    Code
    Public current_wb As Workbook
Public wbkFrom As Workbook



    In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error:
    "object variable or With block variable not set" for any of the following statements in the second macro


    Code
    current_wb.Activate
current_wb.Worksheets(1).Select
wbkFrom.Activate


    I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.

  • Lookup Date In VBA

    I have have been trying to use either vlookup or Match Excel functions in VBA code where the lookup key field is a date. I don't have any problems doing either in the spreadsheet, but both fail in my VBA code. I get an error that the program can't find the "Vlookup [or Match] property of the the worksheetfunction object."


    I have a thought that I could try to convert the date to an integer, and do lookup on the integer, but even if it works it seems like an escape from doing things the right way. I can get worksheetfunction.vlookup to work when I am not using a date as the lookup field.


    Sorry I am not enclosing the code. I have changed it so much trying to find a workable solution that it is not much use now.


    there is some thought that worksheetfunction.vlookup and worksheetfunction.match don't work in VBA when the lookup field is a date. Possibly this is just an Excel glitch, but if there is a way to fix, that would really make things easier. thanks in advance.

  • VBA Vlookup With Date

    I am trying to use worksheetfunction.vlookup. with a date as the lookup item and key field. funcion works when I test using integers, but fails when I use dates.


    Code
    Sub vlok()
c = DateSerial(2007, 12, 2)
c = Application.WorksheetFunction.VLookup("c", Range("tab1"), 2, False)
End Sub

  • AutoComplete / IntelliSense For Worksheets

    I am having some problems understanding the autocomplete function and selecting ranges. When I type the below and hit the "." after "Application," VBA gives me a dropdown with things (objects, methods, properties, ??) that can next be selected. This I like. I select "Worksheets." what I next want to select is a range, but when I close the bracket after "calendar" and hit "." again, I don't get any other dropdowns. My impression at that point is that I have done something incorrectly.


    I use the dropdowns heavily b/c my understanding of what is linked to what in VBA is not strong. In object viewer I see that there is "worksheets" and there is "worksheet." "Worksheet" has a sub-element for "range" (which is what I was wanting) but "worksheet" is not a choice after "application."


    Code
    c = Application.Worksheets("calendar").Range("B14").Select


    Is there something I am doing wrong in using the autocomplete to select a range, and do you have any tips for understanding the autocomplete or object viewer screens.


    Thanks, PS- Did I bracket the code correctly?

  • test first character in each cell to see if alpha

    Re: test first character in each cell to see if alpha


    all, great info, thanks. quick general questions
    1) I've read much of vba book by walkenback. Great stuff, but jumps around. Is there a programming reference that presents vba programming in a more comprehensive fashion? There are so many functions, objects, ... that I don't feel comfortable I know how they really function


    2) Mental glitch: what does the "Set" command do and when do you use it instead of an assignment "=" operator


    3) In conditional formatting using formula, how does it know what row to evaluate...that is, to test A1 for row 1, A2 for row 2? what if you wanted A1 to test A2?


    Help here has been invaluable. I was CS guy in college, but years ago now. Lots of fun to be able to play, but only because of the excellent help I can get when I am stuck. tried the cond formatting...am going to try the macro route too.

  • test first character in each cell to see if alpha

    I want to be able to go through a range (all in col A) and if the first non-blank character is a Letter, change the font and cell color.


    In excel I can use "Trim" to clear leading blanks, then use "code" to test the ascii value of the first character.


    in VBA I can use the trim and assign cell valuable to a variable, but don't know how to pick off first character in string and test to see if it is an alpha (versus a blank, or a number)


    thanks

  • can't see entire x axis labels

    I have a bar graph with positive and a few slightly negative values. the xAxis labels are long...20 characters. I have the x-axis labels rotated 90 degrees.


    I would like to see the entire xaxis label. have tried x-axis labels, alignment, offset, but does not really do correctly even at maximum offset value of 1000.


    Is there a way to set the x-axis labels so that they are completely below the chart, but still in the chart window.


    thanks

  • Delete rows routine skips a row after delete

    Hi all. Let me thank you up front--instead of as replies--for all your answers. I have really valued your replies--there is a lot of nuance to understand here!!


    In the below code segment, I try use a "for each" loop [i read about in another thread] to either hide or delete rows containing a substring. Does not work for deleting, because Excel shifts down a row on delete, then "for each" loop shifts another row--so we skip a row. Any interesting ideas on how to fix?? (see code below)


    Note- I was using a while loop, starting at bottom row and moving up. This accomodated the row shift after deleting current row. I changed, thinking the "for each" loop may be quicker.


    PS-Second small question: Is there a difference between Null and ""? Is there anything called "empty?" how do these show up when you try to test a variable's value? -- It seems like some VBA commands can fail without error msg and I am not sure what then populates a variable assigned by that command.


    code:
    searchstring = Application.InputBox("Enter sub-string to delete/hide", Type:=2)
    For Each cel In test_rng
    If InStr(1, cel.Value, searchstring, 1) >= 1 Then
    If actiontype = vbYes Then
    cel.EntireRow.Delete
    cel.Offset(-1, 0).Select
    'MsgBox ("active cell is " & ActiveCell.Address)
    Else
    cel.EntireRow.Hidden = True
    'MsgBox ("active cell hidden is " & ActiveCell.Address)
    End If
    End If

    Next cel

  • value of variable on error

    In code below, I get user to enter a string. String then compared to all cells in a column and if found (either matches or subset) the row is hidden. Question: in excel, "Search" function returns #value if string NOT found, and I can use ISERROR function to catch the #value. Here the "worksheet function.Iserror" does not seem to work if Searchstring not found in Cel (Cel is each cell in a for loop). So, what is the value of "C" if searchstring not found in cel??


    PS- I don't declare "C" as a type. I guess it should be integer. Would this matter?



    searchstring = Application.InputBox("Enter sub-string to delete/hide", Type:=2)
    For Each cel In test_rng
    C = Null
    C = Application.WorksheetFunction.Search(searchstring, cel)
    If Application.WorksheetFunction.IsError(c) Then
    MsgBox (" string not found in this cell")
    End If
    Next cel

  • Select statement for range

    Hi,


    I used the code segment below (I picked up here) to modify a macro I use. It works great. Didn't work initially in my code b/c I ended the set statement ("set range = ..." ) with".select" What does the select do that causes the For Each loop not to work?


    second question: If I were to use option explicit, what variable type would I make "a" for this code: a = first_cell.Address


    thanks


    Sub checkCol()
    Dim cel As Range, rng As Range
    Set rng = Range("A1", Range("A65000").End(xlUp))
    For Each cel In rng
    If cel.Value = "" Then
    cel.EntireRow.Hidden = True
    End If
    Next cel
    End Sub