[SOLVED] Translate a useful Excel script from VBA to AHK

[clina1j]

So in my current project, I am doing several things in Excel. One of the things I need to do is unmerge cells, then fill them all with the same thing. online, I found a short script to do that for an entire worksheet, see code below.

Code: Select all

Sub UnMergeFill()

Dim cell As Range, joinedCells As Range

For Each cell In ThisWorkbook.ActiveSheet.UsedRange
    If cell.MergeCells Then
        Set joinedCells = cell.MergeArea
        cell.MergeCells = False
        joinedCells.Value = cell.Value
    End If
Next

End Sub

I need to adjust it so that it will do that to whatever range I pass in (as oRange, a range object), and it is written in AHK, so that it will flow with the rest of my script.
I think I translate most of it, but the "For" line worries me. I am nervous about handling objects from Excel in AHK, and fairly certain that I would get it wrong.

Could some one translate that line for me? Or create a work around if there isn't an easy way to do it?
See my attempt at all of the other lines below:

Code: Select all

UnMergeFill(oRange) {
	For ;this is where the For line I am worried about goes, it should be for each "cell" in "oRange"
	{
		if cell.MergeCells then {
			joinedCells := cell.MergeArea
			cell.MergeCells := False
			joinedCells.Value := cell.Value
		}
	}
return

[Guest]

Hey, try this.

Code: Select all

UnMergeFill(oRange) {
	For Cell, in oRange
	{
		if (cell.MergeCells != 0) {
			joinedCells := cell.MergeArea
			cell.MergeCells := 0
			joinedCells.Value := cell.Value
		}
	}
}

HTH

[clina1j]

Thanks! Sorry, I am just nervous about the more complicated "for" syntax

[somethingfly]

I had the same need as you, wanted to translate that same VBA script to AHK. I incorporated "HTH"'s translation and would use it on the entire UsedRange, and that can take a long time. It was annoying me how it cycled through each cell, taking forever. I found this though, https://stackoverflow.com/questions/939 ... lls-in-vba , in particular the answer by "brettdj". It took a bit to modify it for AHK (these, in particular, helped https://autohotkey.com/boards/viewtopic.php?f=7&t=33914 and https://autohotkey.com/boards/viewtopic.php?t=6679 ). According to this "brettdj" person "merged cells were part of xlBlanks". This suggests to me that there might be false positives, xlBLanks might included something not merged. So, for my script, in case it finds a range that is "xlBlanks" but then doesn't find that it contains the "mergecells" property, it'll do the one-by-one script. Someone more advanced than me can say if this is unnecessary, but I don't think it'll hurt. Obviously, for the original example, you'd want to replace "As.UsedRange" with "oRange", remove "AS := Xl.ActiveSheet" and place it all within your "oRange" function.

Code: Select all

    xlBlanks := 4
    xlFormulas := -4123
    xlConstants := 2
    AS := Xl.ActiveSheet
    Loop
    {
        rng1 =
        rng2 =
        Try
            rng1 := XL.Intersect(AS.UsedRange.SpecialCells(xlFormulas), AS.UsedRange.SpecialCells(xlBlanks))
        Try
            rng2 := XL.Intersect(AS.UsedRange.SpecialCells(xlConstants), AS.UsedRange.SpecialCells(xlBlanks))
        rng1address := rng1.Address(0, 0)
        rng2address := rng2.Address(0, 0)
        if rng1
        {
            xlblanknotmerge := "y"
            rng1.Select
            For Cell, in rng1
            {
                if (cell.MergeCells != 0)
                {
                    joinedCells := cell.MergeArea
                    cell.MergeCells := 0
                    joinedCells.Value := cell.Value
                    xlblanknotmerge =
                }
            }
        }
        if rng2
        {
            xlblanknotmerge := "y"
            rng2.Select
            For Cell, in rng2
            {
                if (cell.MergeCells != 0)
                {
                    joinedCells := cell.MergeArea
                    cell.MergeCells := 0
                    joinedCells.Value := cell.Value
                    xlblanknotmerge =
                }
            }
        }
        if ((!rng1) and (!rng2)) or (xlblanknotmerge)
            break
    }
    if xlblanknotmerge
    {
        RangeCount := XL.ActiveSheet.UsedRange.Count
        Tooltip, ...
        Xl.ActiveSheet.UsedRange.Select
        For Cell, in XL.ActiveSheet.UsedRange
        {
            Tooltip, `n unmerging cell %a_index% of %RangeCount% `n press Esc to stop
            if GetKeyState("Esc","P")
            {
                Msgbox, Unmerge as needed manually
                Break
            }
            if (cell.MergeCells != 0)
            {
                joinedCells := cell.MergeArea
                cell.MergeCells := 0
                joinedCells.Value := cell.Value
            }
        }
    }

Here's basically an AHK version of "brettdj" stackoverflow script:

Code: Select all

    xlBlanks := 4
    xlFormulas := -4123
    xlConstants := 2
    AS := Xl.ActiveSheet
    try
        rng1 := XL.Intersect(AS.UsedRange.SpecialCells(xlFormulas), AS.UsedRange.SpecialCells(xlBlanks))
    try
        rng2 := XL.Intersect(AS.UsedRange.SpecialCells(xlConstants), AS.UsedRange.SpecialCells(xlBlanks))
    rng1address := rng1.Address(0, 0)
    rng2address := rng2.Address(0, 0)
    if rng1
        msgbox, Merged formulae cells in %rng1address%
    if rng2
        msgbox, Merged constant cells in %rng2address%
    if !rng1
        msgbox, no formulae cells
    if !rng2
        msgbox, no constant cells