help to convert vba code -big difficult for me

[kfj000]

I found the following code online for vba code,I ran the code and it works, but looking for help how to convert this to ahk.
Thanks in advance for help!

Code: Select all

Sub test()
    arr = [a1].CurrentRegion
    crr = Split("地点，内容，备注", "，")
    x = UBound(arr)
    For j = UBound(arr) To 1 Step -1
        If arr(j, 1) = "序号" Then
            For i = 0 To UBound(crr)
                For l = 1 To UBound(arr, 2)
                    If arr(j, l) = crr(i) Then
                        For k = j To x
                            tm = arr(k, l)
                            arr(k, l) = arr(k, i + 2)
                            arr(k, i + 2) = tm
                        Next k
                    End If
                Next l
            Next i
            x = j - 1
        End If
    Next j
    Sheets(2).[a1].Resize(UBound(arr), UBound(arr, 2)) = arr
End Sub

the Below some code was converted by myself, but it didn't work.

Code: Select all

oExcel := ComObjActive("Excel.Application")
arr := [A1].CurrentRegion
crr := StrSplit("地点，内容，备注", "，")
x := UBound(arr)
j := UBound(arr)
i := 0
l := 1
while (j>=1)
{
	If(arr[j, 1] = "序号")
	{
		while (i<=UBound(crr))
		{
			while (l<=UBound(arr, 2))
			{
				If(arr[j, l] = crr[i])
				{
					k := j
					while (k<=x)
					{
						tm := arr[k, l]
						arr[k, l] := arr[k, i + 2]
						arr[k, i + 2] := tm
						k:=k+1
					}

				}
				l:=l+1
		}

		i:=i+1
	}
	x := j - 1
}
j:=j-1
}
oExcel.ActiveWorkbook.Sheets(2).[A1].Resize(UBound(arr), UBound(arr, 2)) := arr
return

[mikeyww]

Ideas & demonstrations that you can adapt are below.

Code: Select all

oExcel := ComObjActive("Excel.Application")
used   := oExcel.ActiveSheet.UsedRange
rows   := used.Rows.Count
cols   := used.Columns.Count
MsgBox, 64, Used range (cols x rows), %cols% x %rows%
MsgBox, 64, Number of cells used, % used.Count()

MsgBox, 64, Highest positive integer key, % ["a", "b", "z"].Length()
MsgBox, 64, Highest positive integer key, % {10: "a", 5: "b"}.Length()

viewtopic.php?t=8978 • Length method

[flyingDman]

@kfj000 maybe you can explain what that VBA code accomplishes? Perhaps you can show a screenshot of sheet1 and then a screenshot of sheet2. The script appears to be a complicated lookup function and with that many for-loops and ifs, it's very difficult to reverse-engineer.
One thing of interest is the notation [A1].currentregion which appears to be equivalent to xl.range("a1").currentregion.value. In other words, a safearray. I was not aware of that. Unfortunately that notation is not supported by AHK.

[kfj000]

@kfj000 maybe you can explain what that VBA code accomplishes? Perhaps you can show a screenshot of sheet1 and then a screenshot of sheet2. The script appears to be a complicated lookup function and with that many for-loops and ifs, it's very difficult to reverse-engineer.
One thing of interest is the notation [A1].currentregion which appears to be equivalent to xl.range("a1").currentregion.value. In other words, a safearray. I was not aware of that. Unfortunately that notation is not supported by AHK.

Use the vba code to operate the following excel file, and you will get the desired result in sheet2,
The bold font is the header. After running the code, the three headers you want will be neatly arranged

[The extension xlsx has been deactivated and can no longer be displayed.]

[kfj000]

Thanks to @mikeyww and @flyingDman, the code has been adjusted and it can be run, but there is only a little content in sheet2 after running, I don't know what went wrong, the following is the modified code.

Code: Select all

oExcel := ComObjActive("Excel.Application")
arr := oExcel.Range("a1").currentregion.value
crr := StrSplit("地点,内容,备注",",")
x := [arr].Length()
j := [arr].Length()
i := 0
l := 1
while (j>=1)
{
	If(arr[j, 1] = "序号")
	{
		while (i<=[arr].Length())
		{
			while (l<=[arr,2].Length())
			{
				If(arr[j, l] = crr[i])
				{
					k := j
					while (k<=x)
					{
						tm := arr[k, l]
						arr[k, l] := arr[k, i + 2]
						arr[k, i + 2] := tm
						k:=k+1
					}

				}
				l:=l+1
		}

		i:=i+1
	}
	x := j - 1
}
j:=j-1
}
oExcel.ActiveWorkbook.Sheets(2).Range("a1").Resize([arr].Length(), [arr,2].Length()) := arr
return

[mikeyww]

If you display the value of j in the script, what is it?

[kfj000]

If you display the value of j in the script, what is it?

1,The correct result should be 186.The array is written incorrectly.

[mikeyww]

My script shows how to get the number of rows.

[kfj000]

This is the latest modified code.it has worked,I don't know how to write

Code: Select all

[arr,2].Length()

, temporarily replace it with 8.

Code: Select all

oExcel := ComObjActive("Excel.Application")
arr := oExcel.Range("a1").currentregion.value
crr := StrSplit("地点,内容,备注",",")
x := arr.MaxIndex()
j := arr.MaxIndex()
i := 0
l := 1
while (j>=1)
{
	If(arr[j, 1] = "序号")
	{
		while (i<=arr.MaxIndex())
		{
			while (l<=8) ;8=[arr,2].Length()  I don't know how to write this, temporarily replace it with 8
			{
				If(arr[j, l] = crr[i])
				{
					k := j
					while (k<=x)
					{
						tm := arr[k, l]
						arr[k, l] := arr[k, i + 2]
						arr[k, i + 2] := tm
						k:=k+1
					}

				}
				l:=l+1
		}

		i:=i+1
	}
	x := j - 1
}
j:=j-1

}
oExcel.ActiveWorkbook.Sheets(2).Range("a1").Resize(arr.MaxIndex(), 8) := arr ;8=[arr,2].Length()  I don't know how to write this, temporarily replace it with 8
return

Here's Microsoft's explanation for

Code: Select all

[arr,2].Length()

[mikeyww]

My script shows how to get the number of columns.

[flyingDman]

I won't have time to decipher your spreadsheet. At first glance, I would say that it's very "inefficient" as it has various sections that each have their own headers and corresponding headers are in different columns. The script might bring some order to it all, but it remains poorly structured.
It seems that we are trying to get the dimensions of the array. As @mikeyww indicated, his script shows how to get the row and column count. The VBA script uses UBound. The equivalent for that would be maxindex(). maxindex(1) for the 1st dimension and maxindex(2) for the second. See this example:

Code: Select all

xl := ComObjActive("excel.application")
sarr := xl.activesheet.range("a1").currentregion.value

msgbox % "Row count: " h := xl.activesheet.range("a1").currentregion.rows.count
msgbox % "Column count: " w := xl.activesheet.range("a1").currentregion.columns.count
; should be the same as 
msgbox % "Row count: " sarr.maxindex(1)
msgbox % "Column count: " sarr.maxindex(2)

[kfj000]

Thanks to @flyingDman and @mikeyww , with your help, I finally completed this code, I haven't found any running errors for the time being, I'm not good at looping statements, this code is not very beautiful, if you can make this code look like More concise and easy to understand, please give me more advice, I will update it in time.
Here is the minimally modified code:

Code: Select all

oExcel := ComObjActive("Excel.Application")
arr := oExcel.Range("a1").currentregion.value
crr := StrSplit("地点,内容,备注",",")
x := arr.MaxIndex()
j := arr.MaxIndex()
while (j>=1)
{
	If(arr[j, 1] = "序号")
	{ 
		i := 1
		while (i<=crr.MaxIndex())
		{
			l := 1
			while (l<=arr.MaxIndex(2)) 
			{
				If(arr[j, l] = crr[i])
				{
					k := j
					while (k<=x)
					{
						tm := arr[k, l]
						arr[k, l] := arr[k, i + 1]
						arr[k, i + 1] := tm
						k:=k+1
					}
				}
				l:=l+1
			}
		i:=i+1
	  }
	 x := j - 1
  }
	j:=j-1
}
oExcel.ActiveWorkbook.Sheets(2).Range("a1").Resize(arr.MaxIndex(), arr.MaxIndex(2)) := arr
return

[mikeyww]

If it works, go with it.

In case helpful at any point:

The built-in variable A_Index contains the number of the current loop iteration. It contains 1 the first time the loop's body is executed. For the second time, it contains 2; and so on. If an inner loop is enclosed by an outer loop, the inner loop takes precedence. A_Index works inside all types of loops, including file-loops and registry-loops; but A_Index contains 0 outside of a loop.