Posts by pulsar777

Also cross-posted here:
https://www.excelforum.com/exc…xtbody-with-htmlbody.html

Hi,

a syntax that makes Userform controls looks something like this :

Private Sub UserForm_Initialize()


    Dim i As Integer, j As Integer
    Dim k As Integer, m As Integer
    Dim n As Integer, o As Integer
    Dim myControl As Control
    Const hIncrement As Integer = 5
    Const wIncrement As Integer = 10


    ' height = 18
    'width = 72


    n = Me.Height / (18 + hIncrement)
    o = Me.Width / (72 + wIncrement)
    m = wIncrement


    For i = 1 To (o - 1)


        k = 2 * hIncrement
        For j = 1 To (n - 2)


            Set myControl = Me.Controls.Add("Forms.Textbox.1", , True)


            With myControl
                .Top = k
                .Left = m
                .Height = 18
                .Width = 72
                k = j * (hIncrement + .Height) + (2 * hIncrement)
            End With


        Next j


        m = i * (wIncrement + myControl.Width) + wIncrement


    Next i


    Set myControl = Me.Controls.Add("Forms.Commandbutton.1", , True)
    With myControl
        .Height = 50
        .Width = 50
        .Left = Me.Width - .Width - (2 * wIncrement)
        .Top = (Me.Height / 2) - .Height
        .BackColor = vbBlue
        .Caption = "CONSOLE"
        .ForeColor = vbWhite
    End With


End Sub

I'd like to add Click event to a commandbutton.
Any suggestions ?

Hi,

a sample email message would look like :

With eMessage
        .To = "[email protected]"
        .From = """Incredible Hulk"" <[email protected]>"
        .Subject = "Body Splash"
        .TextBody = "Roaaarrr"
        .CreateMHTMLBody "file:///C:/Users/Winner/Desktop/signature.html"
        .Send
    End With

So I would like to combine a text message (TextBody) with an HTML document which would be a signature.
How to do it without modifying html file ?

Thanks !

Hi,
in the attached example, Tree() is run on workbook open, which recalculates sheet1 every second and so makes tree shimmer. I put

DoEvents

to enable user to work with file while macro is run.

But how to let him/her stop the macro without pressing Ctrl + Break, but simply on button click ?

[ATTACH]n1197477[/ATTACH]

Re: List all libraries

Thanks Carim, although some useful stuff to be found there,
I didn't find answer to my question.

Re: List all libraries

Is this even possible without adding all libraries?

Hi,
I'd like to list all available Libraries in first column and each level of their respective class and property to next columns,
to compare Office 2010 with 2016 References.

I've managed to list only active references, using elements from MS Visual Basic Extensibility 5.3 library :

Sub List_References()
    
    Dim vp As VBProject
    Dim r As VBIDE.Reference
    Dim vc As VBIDE.VBComponent
    Dim i As Long
    
    Set vp = ActiveWorkbook.VBProject
    
    For Each r In vp.References
        Debug.Print r.Name, r.Description
    Next r


End Sub

Thank you

Re: Conditional formatting _ array of string criteria

Great, thanks!

Re: Conditional formatting _ array of string criteria

Hi Ger Plante,

thanks. Though, Array often changes and might have even 1000 strings.
Not sure how many separate rules might I make by your code.

Probably to copy Array to some range and change type from
Type:=xlTextString

to

Type:=xlExpression

and apply by a MATCH function?

Or might be there a better way?

Hi all,

I'd like to apply 1 formatting rule,
with multiple text conditions.

Something like this:

With Range("A:A")
    .FormatConditions.Add Type:=xlTextString, String:=Array("01234", "56789"), TextOperator:=xlContains
    .FormatConditions(1).Interior.Color = vbYellow
End With

I'd like to avoid:
a) looping Arrays and Cells on worksheet to apply formatting individually based on If statement (too much looping ..)
b) creating =OR() function in conditional formatting setup, while array would probably exceed that functions arguments limit.

Thank you for any advice!

Re: Get unique items from large array fast

Hi, thank you both, this is really useful.

At 2 columns of 100K of data,
Collections took 5 secs while Dictionaries only 3.1 secs.
Both are case insensitive.
Is there a way to apply Text compare rather than binary? But still, I can overcome it with UPPER worksheet function just in case.

KjBox: the Application.Match failed with type mismatch, cause it probably expected range as 2nd argument (rather than y).

Re: Get unique items from large array fast

KjBox, thanks again.

I think I'll stick with the Collections.
Dictionaries seem to be way slower over such large arrays.

I also found strange that when I had 100K rows rather than 50K,
application could transpose only somewhat over 34K items,
unlike all 50K in original example.

But anyway, your help was very descriptive and appreciated.
Keep up the same good approach !

Re: Get unique items from large array fast

It is relatively fast !
Despite the loop and Redim Preserve

Thanks a lot, KjBox!

Hi everyone,

I'd have 2 large ranges:
A1:A50000
B1:B50000

Both contain duplicates.
I'd like to determine which unique values from A range are contained in B range.

I thought of ruling out these methods:
a) Looping thru arrays and making array of unique values
b) Range.AdvancedFilter Unique:=True
c) Creating pivot tables

I found on Ozgrid this nice looking method:

Dim V As Variant
    With Range("A1:A50000")
        V = Filter(Evaluate("TRANSPOSE(IF(COUNTIF(OFFSET(" & _
            .Address & ",,,ROW(1:" & .Rows.Count & "))," & _
            .Address & ")=1," & .Address & "))"), False, 0)
    End With

But it still takes long processing time.

What would you suggest as fastest way ?

Re: Loop through every combination in Array

forum.ozgrid.com/index.php?attachment/70376/

A really impressive code. Thank you very much.
In this file, I slightly modified your syntax to suit the needs of the original file.

Have a nice day!

Re: Loop through every combination in Array

Also posted here:
http://www.excelforum.com/exce…ru-every-combination.html

Re: Loop through every combination in Array

Or maybe there is other way ?

Hi,
in attached file I made example of a Range method to find by country a combination of 2 values that add up to amount on the right.
However, Belgium doesn't get its Cell Addresses, because 138 is Sum of 3 values (E9, E10, E16).

I look for a looping mechanism, that would go through each 2-combination of values until it finds the result,
otherwise would continue with 3-combination, 4 .. up to all items combination necessary to find respective SUM.

Example:
A-B, A-C, A-D ..... A-Z
A-B-C, A-B-D ...A-B-Z ; A-C-D .. A-C-Z ...

I thought of using an Array, but don't know how.

[ATTACH=CONFIG]70345[/ATTACH]

Re: Find out if formula is absolute

Hi mikerickson,

same result whether I loop through areas or cells.
Cannot figure out why, I guess I need to find a workaround.

Re: Find out if formula is absolute

Hi Luke,

yes, basically I used same method - Application.ConvertFormula

Private Sub Convert_Formulas_Absolute()


    Dim r As Range
    Dim ws As Worksheet
    Dim i As Integer, j As Integer
       
    For i = 3 To 11
    
        Set ws = Worksheets(i)
        Set r = ws.Range("F18", ws.Range("G82")).SpecialCells(xlCellTypeFormulas)
        
        For j = 1 To r.Areas.Count
            r.Areas(j).Formula = application.ConvertFormula( _
                        r.Areas(j).Formula, xlA1, xlA1, xlAbsolute)
        Next j
        
    Next i
 
End Sub

Let's say some cells of the range contain formula like this:

=SUMIF('INPUT TRAD'!$F:$F,BNL!$D$20,'INPUT TRAD'!$B:$B)

Then those cells after running the macro change result to #VALUE error.