Help in converting a VBA script - Xtreme Visual Basic Talk

08-10-2012, 07:23 PM

vinniehnk

Newcomer

Join Date: Aug 2012

Posts: 1

Help in converting a VBA script

Hello all,

I am hoping I am posting this in the right area. I have a VBA script in excel that I am trying to convert to a VB.Net script. Currently, the script takes any text entered into Cell D4 and splits into a new line every 53 characters. My end goal is to Have a user-form with 3 check boxes and a text box. The three text-boxes will be labeled "w, s, and L" and each box will format this text differently. This works currently in excel, I am just trying to branch out.

My VBA code is:

Sub Parse(): Dim S() As String
Dim j As Integer, i As Integer
j = Int(Len(Cells(4, 4)) / 53 + 1)
ReDim S(j + 1)
j = 0: S(0) = Cells(4, 4)
ParseText:
If Len(S(0)) > 53 Then
S(j + 1) = Left(S(0), 53)
i = InStrRev(S(j + 1), " ")
S(j + 1) = Left(S(j + 1), i - 1)
S(0) = Mid(S(0), i, Len(S(0)))
j = j + 1: GoTo ParseText: End If
DisperseText:
S(j + 1) = S(0)
For i = 1 To j + 1
Cells(4 + i, 4) = S(i): Next i: End Sub

If I did post in the wrong area, I apologize. Please direct me to the correct area.

08-11-2012, 06:14 PM

Gruff

Bald Mountain Survivor

Super Moderator
* Expert *

Join Date: Aug 2003

Location: Oregon, USA

Posts: 5,877

Seems a bit more compicated than it needs to be.

The following should be much easier to convert to vb.net

Code:

Option Explicit

Private Sub CommandButton1_Click()
  Call Parse(4, 4, 53)
End Sub

Public Sub Parse(byval Row As Integer,byval  Col As Integer, ByVal chunk As Integer)
  Dim stext As String
  Dim total As Integer
  Dim i As Integer

  stext = Cells(Row, Col)
  total = Len(stext)

  For i = 0 To total Step chunk
    Dim sseg as string
    sseg = Trim(Mid(stext, i + 1, chunk))
    if sseg <> "" then
      Cells(Row, Col) = sseg
      Row = Row + 1
    end if
  Next i
End Sub

So perhaps something like this,,,

Code:

Public Sub Parse(oSheet As Excel.Worksheet, Row As Integer, Col As Integer, ByVal chunk As Integer)
    Dim stext As String = oSheet.Cells(Row, Col)
    Dim total As Integer = Len(stext)

    For i As Integer = 0 To total Step chunk
      Dim sseg As String =  stext.substring(i,chunk).trim
      If sseg <> "" Then
        oSheet.Cells(Row, Col) = sseg
        Row = Row + 1
      End If
    Next i
  End Sub

__________________
Burn the land and boil the sea
You can't take the sky from me

~T

Last edited by Gruff; 08-11-2012 at 06:39 PM.