Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.

# Macro Excel Script Understanding

4 replies to this topic

### #1 SammiD

SammiD

• Members
• 7 posts
• OFFLINE
•
• Local time:10:53 PM

Posted 18 June 2014 - 02:04 PM

Hello,

I am kinda new to programming and i wanted someone here to help me understand this code inExcel Macro
The purpose of the code, is to start the data collection and log it on the same sheet. Below is the code for the start button .

Thank you and your help is greatly appreciated

Sub Start()
'
' Rockwell Automation
'

Worksheets("sheet1").Range("D1").Value = "hh:mm:ss"
Worksheets("sheet1").Range("E1").Value = "Date"

Dim hours As String
Dim mintues As String
Dim seconds As String
Dim time As String
Dim DArray1(100) As String
Dim DArray2(100) As String
Dim number_rows As Integer
hours = Worksheets("sheet1").Range("B2").Value
mintues = ":" & Worksheets("sheet1").Range("B3").Value
seconds = ":" & Worksheets("sheet1").Range("B4").Value
number_rows = Worksheets("sheet1").Range("B5").Value
time = hours & mintues & seconds

j = 1
Do While (Worksheets("sheet1").Cells(j + 8, 2).Value <> 0)
linkp = Worksheets("sheet1").Cells(j + 8, 2).Value
p1 = InStr(1, linkp, "[", vbTextCompare)
p2 = InStr(1, linkp, "]", vbTextCompare)
p3 = InStr(1, linkp, ",", vbTextCompare)
Worksheets("sheet1").Cells(1, j + 5).Value = linkp4
j = j + 1
Loop

For i = 1 To number_rows Step 1
value1 = Now
Value2 = Date
Worksheets("Sheet1").Cells(i + 1, 4).Value = value1
Worksheets("Sheet1").Cells(i + 1, 5).Value = Value2
For k = 1 To j - 1 Step 1
RSIchan = DDEInitiate("RSLinx", DArray1(k))
Value3 = DDERequest(RSIchan, DArray2(k))
Worksheets("Sheet1").Cells(i + 1, 6 + k - 1).Value = Value3
DDETerminate (RSIchan)
Next
Application.Wait Now + TimeValue(time)
Next

End Sub

### #2 Billy O'Neal

Billy O'Neal

Visual C++ STL Maintainer

• Malware Response Team
• 12,304 posts
• OFFLINE
•
• Gender:Male
• Location:Redmond, Washington
• Local time:07:53 PM

Posted 18 June 2014 - 04:04 PM

What have you done so far? Try commenting each block of code with what you think it does, updating as you go.

Just dumping a bunch of code on the forum and asking people to explain it to you will teach you nothing about writing software.

Billy3
Twitter - My statements do not establish the official position of Microsoft Corporation, and are my own personal opinion. (But you already knew that, right?)

### #3 SammiD

SammiD
• Topic Starter

• Members
• 7 posts
• OFFLINE
•
• Local time:10:53 PM

Posted 18 June 2014 - 09:10 PM

I tried that, but I can't run the code without the proper VPN connection which i don't have, I got it from a friend, but he doesn't do programming. I understand the first part of the code, but once i get into the second part of the code..

p1 = InStr(1, linkp, "[", vbTextCompare)
p2 = InStr(1, linkp, "]", vbTextCompare)
p3 = InStr(1, linkp, ",", vbTextCompare)

I get confused with the variables used; like linkp, p1, p2.

It isn't for school, it is for personal understanding

### #4 JohnnyJammer

JohnnyJammer

• Members
• 1,117 posts
• OFFLINE
•
• Gender:Male
• Location:QLD Australia
• Local time:12:53 PM

Posted 18 June 2014 - 11:29 PM

This is a rough how to, but like billy said you wont learn unless you do it and read it your self. It sounds hard and is always hard trying to work out someone elses code because there are 10 ways to do 1 thing when it comes to programming.

I prefere scripting but am forced to use vs2010 at work for applications the company i work for need.

```Dim hours As String ' Strings are character based
Dim mintues As String
Dim seconds As String
Dim time As String
Dim DArray1(100) As String 'sets the limit on this array to 100, a bit silly if you ask me and a bad move, what ifits over 100?
Dim DArray2(100) As String
Dim number_rows As Integer ' Integers are single digit numbers
hours = Worksheets("sheet1").Range("B2").Value ' this gets the values from sheet1 and uses the cell_array (.Range)
mintues = ":" & Worksheets("sheet1").Range("B3").Value 'this gets the sheet2 value from b4 same as the oen above gets it from b3 There is not error handling either so if the data Not Exist it will throw an error
seconds = ":" & Worksheets("sheet1").Range("B4").Value 'same as above
number_rows = Worksheets("sheet1").Range("B5").Value 'same ^^
time = hours & mintues & seconds ' join the 3 variables together

************************
j = 1 ' cannot find where j is declared?
Do While (Worksheets("sheet1").Cells(j + 8, 2).Value <> 0) ' starts the do while loop as long as its less than or more than 0
p1 = InStr(1, linkp, "[", vbTextCompare) ' starts comparing data at position 1 and looks for [
p2 = InStr(1, linkp, "]", vbTextCompare) 'same above
p3 = InStr(1, linkp, ",", vbTextCompare) 'same above
linkp2 = Mid(linkp, p1 + 1, p2 - p1 - 1) ' no dec for linmp2 or 3 or 4,
linkp3 = Mid(linkp, p2 + 1, Len(linkp)) ' mid counts in linkp starting at p1(Position 1) + 1 (Its actually position 2) to the length of linkp which would be j + 8 (j is used to count how many times its looped)
linkp4 = Mid(linkp, p2 + 1, p3 - p2 - 1) ' counts start from mid(linkp, position2(p2) + 1 to positioon3(p3) - p2 - 1 more (looks odd to me)
DArray1(j) = linkp2 'sets some dataup in the array
DArray2(j) = linkp3 ' same as above
Worksheets("sheet1").Cells(1, j + 5).Value = linkp4 'sets the data from the loop into cheet1,cells1 down, j)being the number of times its looped, + 5
j = j + 1 ' add another j(1) to the last j or j += 1
Loop 'redo loop
```

im assuming linjkp is the linkposition

Edited by JohnnyJammer, 18 June 2014 - 11:32 PM.

### #5 JohnnyJammer

JohnnyJammer

• Members
• 1,117 posts
• OFFLINE
•
• Gender:Male
• Location:QLD Australia
• Local time:12:53 PM

Posted 18 June 2014 - 11:36 PM

Personalyl you should always start off in notepad (Creating batch file scripts of python/Ruby), using a IDE which does half the work for you isnt the best way to start IMO.

I sort of started with Ruby and VB6(Still use it), then and now i use VS2010 .net 3.5 ^, Python, VBS, VBA (For creating Microsoft applications/Macros for office suite), PowerShell.

i aint no programmer in anyway and it isnt my job but if you want to be a good SysAdmin you have to know a few languages.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users