Jump to content
TimG

Error: bad argument value: string position out of range 17

Recommended Posts

TimG

I have been having problems loading a lisp that has previously worked, without a problem for years, but now throws a message "Error: bad argument value: string position out of range 17".

 

The lisp is loaded on opening of each drawing in the acaddoc.lsp, which has not changed. It is supposed to open a csv and update blocks.

 

From what I can tell, it seems as though AutoCAD cant find the csv file in the Working Directory. (When opening a drawing with the lisp loaded in acaddoc.lsp specifying the name via findfile "name.csv")

 

When I load the lisp through APPLOAD and manually select the file, via getfiled, it works. (findfile Nil)

 

 

The Name of the csv has not changed in the acaddoc.lsp and the file is definitely in the same folder as the opened drawing.

 

So my questions are,

 

Is there any Variable in AutoCAD (Map 3d 2016) that could have changed to cause this?

 

Is it a bug?

 

Are there methods of finding the specified file in the same directory, (as the opened drawing), other than findfile? vla-get-FoundPath perhaps?

 

How can I debug using VLISP?

 

I tried (VL-BT) and get this;

 

Backtrace:

[0.19] (VL-BT)

[1.15] (#)

[2.12] (# "(vl-bt)" T #)

:CALLBACK-ENTRY.6 (:CALLBACK-ENTRY)

:ARQ-SUBR-CALLBACK.3 (nil 0)

nil

 

I'm not sure what it means?

It's the first time I've used VLISP and only have a basic knowledge of VBA through a TAFE course, which was really more about VBS.

 

At this stage I'm not sure if its a setting or AutoCAD is not looking in the right place?

 

I have tried resetting autocad to default which didn't work, then I reinstalled autocad, which also didn't work. Then I updated to windows 10 just to see if some setting was changed or broken. None of those steps worked.

 

I had been playing around with excel,( and had improved the spreadsheet which output the csv to also put out a xlsx, for a Data Link), in the previous weeks, which gave me the possibility that I had unwittingly changed a setting somewhere. The excel spreadsheet had worked with the lisp for at least 2 weeks before the error started to occur.

 

One other thing to mention is that when excel is trying to save the csv and autocad is reading it,( has it open maybe? through the manual loading of it through the lisp), it comes up with an error 1004 or something. This happened to a co-worker who tried to debug it, and we tried alternate ways of saving the csv.

 

After that moment the problems started.

 

We tried the original untouched excel spreadsheet, acaddoc.lsp and method like we always had done, but now the error always occurs.

 

Please, are there any suggestions?

Share this post


Link to post
Share on other sites
Tharwat

Hi,

 

Welcome to CADTutor.

 

Here I am talking about AutoCAD in general and not about Map 3D and the following answer is just a work around and might not be the desired solution.

 

Remove the findfile function and add the full name of the *.csv excel file to open function if your directory work is not changing from drawing to another.

If the the above suggestion did not work and your program is not that big, you can post the codes and many users here could help as best as they can including me.

 

Good luck.

Share this post


Link to post
Share on other sites
rlx

Hi TimG,

 

 

Maybe your code has not changed but your excel sheet? The cell formatting , so you should check if the value passed from excel to your lsp returns the value you want.

 

 

gr, Rlx

Share this post


Link to post
Share on other sites
Jef!

I noticed that findfile doesnt always work as expected on the current drawing folder. (i think) its depending on the way you open the drawing (using open, double clicking or drag and drop).

 

Are there methods of finding the specified file in the same directory, (as the opened drawing), other than findfile? vla-get-FoundPath perhaps?

Yeah, you can use the variable dwgprefix, it gives the drawing path

(strcat (getvar 'dwgprefix) TheFileName)

 

Cheers

Share this post


Link to post
Share on other sites
Lee Mac
I have been having problems loading a lisp that has previously worked, without a problem for years, but now throws a message "Error: bad argument value: string position out of range 17".

 

This error typically results from evaluating the vl-string-search or vl-string-position functions with the starting index argument outside of the range of available characters, e.g.:

_$ (vl-string-search "a" "abc" 4)
; error: bad argument value: string position out of range 4

_$ (vl-string-position 65 "abc" 4)
; error: bad argument value: string position out of range 4

Therefore, to debug the program, I would search the code for either of these functions, or follow the first section of my tutorial here.

 

I would then analyse the string argument being passed to the function, as the expression is evidently expecting a string with length greater than 18 characters.

 

EDIT: I have now updated my Error Message Troubleshooter to include this error.

Share this post


Link to post
Share on other sites
TimG

Hi Tharwat, Thanks for your reply.

 

I copied the full path to the drawing folder and the csv name into the acaddoc.lsp, and opened one of the drawings to test if it works.

 

The result was interesting, I thought, because it returned a message that the csv was "not found", that contained no back slashes "/ " and also had "Ž4" after the first folder.

 

JOBSŽ4 xxxxxx xxx - xxxxxxx xx xxxxxxxxxxAutoCadDWGREG.csv not found. - THE RESULT

 

J:\JOBS\6164 xxxxxx xxx - xxxxxxx xx xxxxxxxxxx\AutoCAD\DWGREG.csv - WHAT IT SHOULD BE

 

[Note: I just censored the project name for company privacy.]

 

Is the 616 being replaced by "Ž"?

 

Why have the "\" disappeared from the name?

Share this post


Link to post
Share on other sites
TimG

Hi rlx,

 

I know that the formatting has definitely changed throughout the trial and error coding attempts I made before I got it working.

 

To get the xlxs to populate my autocad table in the font we use, I changed it to isocp. I was trying to get the data link to bring the text through the same as in the excel sheet, with underlined titles and text style.

 

In that process I had changed other cells in the original workbook sheets by mistake, so I cleared all cell formatting and thought I had reset the text font back to Calibri as it was originally.

I also coded it to clear contents and formatting of the destination sheet each time the create xlxs button was pressed.

 

However, in the process of elimination, I created a new csv manually from scratch just to test the acaddoc.lsp, which still returned the error.

Also using earlier versions of the excel spreadsheet still failed to work as before.

 

Just for clarity, the xlxs populates the Drawing Index table with the titles and drawing numbers, and the DWGREG.csv fills out the Title blocks in the drawing set.

 

The csv is what is problematic and it is copied and pasted out of the parent workbook without formatting changes. Although as noted, the parent workbook underwent many unintended changes before I got it to work as I envisioned it.

 

It did all work for about 2 weeks before my error started to occur.

Share this post


Link to post
Share on other sites
TimG

Hi Jef!,

 

Nice use of "!" in your name by the way. It makes it seem like everyone who replies to you is extra excited!

 

Pasting your suggestion into the command line returned the desired results.

 

Command: (strcat (getvar 'dwgprefix) "dwgreg.csv")

 

"J:\\JOBS\\6164 xxxxxxx xxx - xxxxxxxx xx xxxxxxxxxxxx\\AutoCad\\dwgreg.csv"

 

Both capitals and lowercase worked for the name and returned the same result, just to eliminate that variable for my own understanding.

 

Its good to know that it can be found by that method. Thanks.

Share this post


Link to post
Share on other sites
TimG

Hi Lee Mac!!,

 

I was hoping for your help.

 

It is your Update attributes (UpdateTitleBlock.lsp) that we have been using without fail, since before I started working here, and I'm sorry to say that I broke it...

 

Well, I have in no way altered it, but in any case, have somehow caused something that it doesn't like. (It may not have actually been me, I would like to think, but in light of my recent misadventures into VBA; it is more than likely.)

 

This is where it breaks with the error,

 

(defun LM:csv->lst ( str sep pos / s )

(cond

( (not (setq pos (vl-string-search sep str pos)))

(if (wcmatch str "\"*\"")

(list (LM:csv-replacequotes (substr str 2 (- (strlen str) 2))))

(list str)

)

)

( (or (wcmatch (setq s (substr str 1 pos)) "\"*[~\"]")

(and (wcmatch s "~*[~\"]*") (= 1 (logand 1 pos)))

)

(LM:csv->lst str sep (+ pos 2))

)

( (wcmatch s "\"*\"")

(cons

(LM:csv-replacequotes (substr str 2 (- pos 2)))

(LM:csv->lst (substr str (+ pos 2)) sep 0)

)

)

( (cons s (LM:csv->lst (substr str (+ pos 2)) sep 0)))

)

)

 

Note also, my reply above that results with the "Ž" value returned when I pasted the full file path as utb:csv instead of just the name.

 

(setq utb:csv "J:\JOBS\6164 xxxxxxx xxx - xxxxxxxx xx xxxxxxxxxxxx\AutoCad\dwgreg.csv") ;; e.g. (setq utb:csv "C:/myfolder/myfile.csv") - As a test

 

(setq utb:csv "dwgreg.csv") ;; e.g. (setq utb:csv "C:/myfolder/myfile.csv") - What has always previously worked, until last week.

 

Do you know what causes the "Ž" to occur?

 

Thanks for your contributions, I have seen a lot of your work and help on the various forums as I have been trying to learn on the job with this project of late, and you show a heavy presence.

 

Note to others: I'm not down playing your help, as it is all very much appreciated.

 

***EDIT***

 

I noticed that copying the path from explorer uses the single "\". Which is neither "\\" or "/" , as I understand is the correct Syntax to use in a .lsp.

 

On testing,

 

(setq utb:csv "J:\\JOBS\\6164 xxxxxxx xxx - xxxxxxxx xx xxxxxxxxxxxx\\AutoCad\\dwgreg.csv") ;; e.g. (setq utb:csv "C:/myfolder/myfile.csv") - As a test

 

This worked as expected.

 

 

(setq utb:csv "J:/JOBS/6164 xxxxxxx xxx - xxxxxxxx xx xxxxxxxxxxxx/AutoCAD/dwgreg.csv") ;; e.g. (setq utb:csv "C:/myfolder/myfile.csv") - As a test

 

This worked as expected.

 

 

(setq csvfile "DWGREG.csv") ;; e.g. (setq csvfile "C:/myfolder/myfile.csv") - As a test (both lower and uppercase)

 

Error: bad argument value: string position out of range 17

 

 

(setq csvfile "/DWGREG.csv") ;; e.g. (setq csvfile "C:/myfolder/myfile.csv") - As a test (both lower and uppercase)

 

DWGREG.csv not found.

 

(setq csvfile "\\DWGREG.csv") ;; e.g. (setq csvfile "C:/myfolder/myfile.csv") - As a test (both lower and uppercase)

DWGREG.csv not found.

 

I'm not sure if this helps, but it would partly explain where the "Ž" came from.

Edited by TimG

Share this post


Link to post
Share on other sites
TimG

This is the Module attached to the Create CSV button in excel.

 

I tried to add checks along the way to see what was happening.

My goal in part was to stop the error 1004 for coming up while trying to saveas if the csv was already open.

It does seem as though AutoCAD has it open sometimes if loaded manually through the UpdateTitleBlock.lsp with the file name as Nil.

 

(setq csvfile Nil) ;; e.g. (setq csvfile "C:/myfolder/myfile.csv")

 

I'm not sure how to determine what autocad does at all, or how it affects the state of the file.

 

As you may see I have just copied and pasted code from the net and tried to get it to work.

The first section is how it used to be before I started playing with it. (This was a week after the errors occurred so I don't think it caused it at all)

 

Sub SaveAsCSV()

'

' export Macro

 

'strName = ThisWorkbook.Path & "\" & "dwgreg.csv"

 

'Range("3:500").Select

'Selection.Copy

'Workbooks.Add

'ActiveSheet.Paste

'ActiveWorkbook.SaveAs filename:=strName, FileFormat:=xlCSV, CreateBackup:=False

'Application.DisplayAlerts = False

'ActiveWorkbook.Close

'Application.DisplayAlerts = True

 

'Application.ScreenUpdating = True

 

'MsgBox "File has been Created and Saved as: " & vbCr & strName, , "Copy & Save Report"

 

'end Sub

Dim wbS As Workbook

 

Dim wbS2 As Workbook, wbT2 As Workbook

Dim wsS2 As Worksheet, wsT2 As Worksheet

 

Set wbS = ThisWorkbook

Set wbS2 = ThisWorkbook 'workbook that holds this code

Set wsS2 = wbS2.ActiveSheet

 

 

wsS2.Copy

Set wbT2 = ActiveWorkbook 'assign reference asap

 

 

Set wsT2 = wbT2.Worksheets("MRWA")

wsT2.Name = "DRAWING REGISTER" 'rename sheet

 

wsT2.Range("1:2").Select

Selection.DELETE

 

ActiveSheet.Shapes.Range(Array("Button 1")).Select

Selection.DELETE

 

Cells.Select

Selection.ClearFormats

 

'MsgBox wbS2.Path, , "PATH"

 

 

'Test to see if the folder path existsSheets("Drawing Index").

 

 

Dim FolderPath2 As String

 

FolderPath2 = wbS2.path

If Right(FolderPath2, 1) "\" Then

FolderPath2 = FolderPath2 & "\"

End If

 

If Dir(FolderPath2, vbDirectory) vbNullString Then

MsgBox "Folder exists"

Else

MsgBox "Folder doesn't exist"

End If

 

 

'test if file exists

 

 

Dim FilePath2 As String

Dim TestStr2 As String

 

FilePath2 = wbS2.path & "\DWGREG.csv"

 

'MsgBox FilePath2, , "FILE PATH"

 

TestStr2 = ""

On Error Resume Next

TestStr2 = Dir(FilePath2)

 

'MsgBox TestStr2 & "= File Name", , "Test string equals " & TestStr2

 

On Error GoTo 0

If TestStr2 = "" Then

MsgBox "File doesn't exist", , "FILE NULL"

Else

MsgBox "File exists", , "TestStr2 = " & TestStr2

End If

 

 

 

 

 

'Test if file is open

 

'MsgBox "Is Drawing Open?", vbOKOnly, "TEST"

 

 

If bIsBookOpen("DWGREG.csv") Then

 

MsgBox ("DWGREG.csv is open!" & vbCr + vbCr + "CLOSE THE SPREADSHEET and TRY AGAIN"), , "DRAWING REGISTER IS OPEN"

 

ActiveWorkbook.Close SaveChanges:=False

 

 

Exit Sub

 

 

 

Else

 

'MsgBox "The Book is not open!", , "DRAWING REGISTER IS NOT OPEN"

 

End If

 

 

Dim InputFolder As String

Dim OutputFolder As String

 

InputFolder = wbS.path

 

'MsgBox InputFolder, , "input folder"

 

'MsgBox ActiveWorkbook.FullName, , "Full Name"

 

'MsgBox ActiveWorkbook.path, , "Path"

 

'MsgBox wbS.FullName

 

 

'save new workbook

 

wbT2.SaveAs filename:=wbS.path & "\DWGREG", FileFormat:=6, CreateBackup:=False

 

Application.DisplayAlerts = False

 

Application.ScreenUpdating = False

 

 

ActiveWorkbook.Close

 

Application.DisplayAlerts = True

 

Application.ScreenUpdating = True

 

'MsgBox wbS.FullName

 

'MsgBox ActiveWorkbook.Path

 

strName2 = wbS2.path & "\DWGREG.csv"

 

MsgBox "File has been Created and Saved as:" & vbCr & strName2, , "COPY & SAVE REPORT"

End Sub

Share this post


Link to post
Share on other sites
TimG

This is the Module attached to the Create xlxs Button.

 

The purpose is to take attribute information and concatenate those into 1 column titled "Description", and then have the drawing number attribute fill the second column titled "Drawing No.".

 

For ease of use in the Drawing index Table that is in AutoCAD, I also have blank rows and underlined Subheadings in the Parent Workbook which I add as required.

 

As an example, CROSS SECTIONS, would be manually added above a number of cross section drawings, then a blank row and underlined subheading of the next subcategory of Typical Details.

This is the new feature I was initially trying to add to the workbook, as previously it was just a spreadsheet to populate title blocks. The Drawing index had to be typed manually as MText in the drawing, and needed to be manually updated as the Drawing register changed.

 

I would like to be able to carry across the Underlined text and have the Table in AutoCAD have the text style of iso. Iso didn't seem to work, but isocp did. The Underlines are lost.

 

This is also just a copy and paste collage of code, that gave satisfactory results in achieving what I wanted to do.

For the Two weeks that it worked, I was pretty happy with what I had done as a VBA novice.

 

Obviously not good enough to put on the fridge, but IT IS my first attempt...

 

 

 

Sub SaveAsXLXS()

'

' export Macro

 

 

Dim wbS As Workbook, wbT As Workbook

Dim wsS As Worksheet, wsT As Worksheet

 

 

Set wbS = ThisWorkbook 'workbook that holds this code

Set wsS = wbS.Worksheets("DRAWING INDEX")

 

wsS.Copy

Set wbT = ActiveWorkbook 'assign reference asap

 

Set wsT = wbT.Worksheets("DRAWING INDEX")

wsT.Name = "DRAWING INDEX" 'rename sheet

 

MsgBox wbS.path, , "PATH"

 

 

'Test to see if the folder path exists.

 

 

Dim FolderPath As String

 

FolderPath = wbS.path

If Right(FolderPath, 1) "\" Then

FolderPath = FolderPath & "\"

End If

 

If Dir(FolderPath, vbDirectory) vbNullString Then

MsgBox "Folder exists"

Else

MsgBox "Folder doesn't exist"

End If

 

 

'test if file exists

 

 

Dim filePath As String

Dim TestStr As String

 

filePath = wbS.path & "\DRAWING INDEX.xlsx"

 

MsgBox filePath, , "FILE PATH"

 

TestStr = ""

On Error Resume Next

TestStr = Dir(filePath)

 

MsgBox TestStr & "String Empty", , "Test string equals " & TestStr

 

On Error GoTo 0

If TestStr = "" Then

MsgBox "File doesn't exist", , "String Empty"

Else

MsgBox "File exist", , "TestStr = " & TestStr

End If

 

 

 

 

 

'Test if file is open

 

MsgBox "Is Drawing Open?", vbOKOnly, "TEST"

 

 

If bIsBookOpen("DRAWING INDEX.xlsx") Then

 

MsgBox ("DRAWING INDEX.xlsx is open!" & chr13 + "CLOSE THE SPREADSHEET and TRY AGAIN"), , "DRAWING INDEX IS OPEN"

 

ActiveWorkbook.Close SaveChanges:=False

 

 

Exit Sub

 

 

 

Else

 

MsgBox "The Book is not open!", , "DRAWING INDEX IS NOT OPEN"

 

End If

 

 

 

'save new workbook

 

wbT.SaveAs filename:=wbS.path & "\DRAWING INDEX", FileFormat:=51, CreateBackup:=False

 

Application.DisplayAlerts = False

 

Application.ScreenUpdating = False

 

 

'MsgBox ActiveWorkbook.FullName

 

'MsgBox ActiveWorkbook.Path

 

Sheets("DRAWING INDEX").Select

Range("A2:B2").Select

Selection.UnMerge

 

Range("A3").Select

activecell.FormulaR1C1 = "DESCRIPTION"

Selection.Font.Underline = xlUnderlineStyleSingle

 

Range("B3").Select

activecell.FormulaR1C1 = "DRAWING No."

Selection.Font.Underline = xlUnderlineStyleSingle

 

Range("A2").Select

activecell.FormulaR1C1 = "DRAWING INDEX"

Selection.Font.Underline = xlUnderlineStyleSingle

 

'

' DELETE BUTTON

'

ActiveSheet.Shapes.Range(Array("Button 1")).Select

Selection.DELETE

 

' DELETE ROW 1

 

Rows("1:1").Select

Selection.DELETE Shift:=xlUp

 

' SAVE AND CLOSE

 

ActiveWorkbook.Save

ActiveWorkbook.Close

 

Application.DisplayAlerts = False

 

Application.ScreenUpdating = False

 

' MESSAGE BOX

 

strName = wbS.path & "\DRAWING INDEX.xlsx"

 

MsgBox "File has been Created and Saved as:" & vbCr & strName, , "COPY & SAVE REPORT"

 

End Sub

 

Sub CommandButton2_Click()

 

 

'CLEAR DRAWING INDEX

 

Sheets("DRAWING INDEX").Select

 

' resize_page Macro

'

 

Cells.Select

Selection.ClearContents

Selection.ClearFormats

Cells.Select

Selection.RowHeight = 15

Cells.Select

Selection.ColumnWidth = 8

 

Selection.UnMerge

Range("A1").Select

 

'SELECT MRWA SHEET

 

Sheets("MRWA").Select

 

 

Range("F4:F100,E4:E100,C4:C100").Copy Destination:=Worksheets("Drawing Index").Range("A4")

 

Sheets("Drawing Index").Range("A4:A100").Copy Destination:=Worksheets("Drawing Index").Range("E4")

 

Sheets("Drawing Index").Range("C4:C100").Cut Destination:=Worksheets("Drawing Index").Range("A4")

 

 

 

 

Sheets("Drawing Index").Range("A2").Value = "DRAWING INDEX"

'Selection.Font.UNDERLINE = xlUnderlineStyleSingle

 

Sheets("Drawing Index").Range("A3").Value = "DESCRIPTION"

'Selection.Font.UNDERLINE = xlUnderlineStyleSingle

 

Sheets("Drawing Index").Range("E3").Value = "DRAWING No."

'Selection.Font.UNDERLINE = xlUnderlineStyleSingle

Sheets("Drawing Index").Select

Range("A3").Select

'ActiveCell.FormulaR1C1 = "DESCRIPTION"

Selection.Font.Underline = xlUnderlineStyleSingle

 

Range("E3").Select

'ActiveCell.FormulaR1C1 = "DRAWING No."

Selection.Font.Underline = xlUnderlineStyleSingle

 

 

Range("A2").Select

'ActiveCell.FormulaR1C1 = "DRAWING INDEX"

Selection.Font.Underline = xlUnderlineStyleSingle

 

Sheets("Drawing Index").Select

 

Range("A:C").EntireColumn.AutoFit

 

 

Call CONCAT3

 

Call COPYPASTE

 

Call resize

 

Call MERGEDEL

 

Call SaveAsXLXS

 

End Sub

 

 

 

Sub CONCAT3()

' Insert column

 

Columns("A:A").Select

Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

 

''CONCATANATION

 

With Range("A100", Range("A" & Rows.Count).End(xlUp))

.Offset(, 0).FormulaR1C1 = "=ConcatenateRange(RC[1]:RC[2],"" - "" )"

 

End With

 

End Sub

 

Sub COPYPASTE()

 

 

'

 

' COPYPASTE Macro

 

Range("A4:A100").Select

Selection.Copy

 

Range("B4:B100").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

 

Columns("A:A").Select

Application.CutCopyMode = False

Selection.DELETE Shift:=xlToLeft

 

Columns("E:E").Select

Selection.Cut

Columns("B:B").Select

ActiveSheet.Paste

 

Columns("B:B").EntireColumn.AutoFit

Columns("A:A").EntireColumn.AutoFit

 

End Sub

 

 

Sub MERGEDEL()

 

' MERGEDEL Macro

 

 

 

Range("A2:B2").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.MERGE

 

Range("A1:B1").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.MERGE

 

Range("B3:B100").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

End With

Selection.MERGE True

 

End Sub

 

 

 

 

Function ConcatenateRange(Parts As Range, Separator As String) ' Build a single string from a passed range with a

' passed separator between each value

 

Dim strTemp As String, sepTemp As String

' declare strTemp and sepTemp as Strings. A String is a data type.

 

Dim Cell As Range

' declares cell as variable to store a cell reference

 

 

Dim cnt As Integer

' declares variable cnt and stores result as an Integer value

 

 

strTemp = ""

' defines the String strTemp as blank

 

 

For Each Cell In Parts.Cells

If Cell.Value = "" Or Cell.Value = 0 Then

' If value of cell is blank or = 0

sepTemp = ""

' the variable sepTemp will be stored as blank

Else

'If value of cell is not blank or = 0

sepTemp = Separator

' the variable sepTemp will store the separator defined by the user

End If

If Len(strTemp) = 0 Then

' if the length of the variable stored in strTemp = 0

strTemp = CStr(Cell.Value)

' the variable strTemp will be equal to the value of the current cell.

Else

' if the stored variable strTemp is not equal to 0

strTemp = strTemp & sepTemp & CStr(Cell.Value)

' the variable strTemp will equal the current cell value and concatenate with the user defined separator

End If

Next Cell

ConcatenateRange = strTemp

End Function

 

Sub resize()

'

 

' text_style Macro

'

 

'

Columns("A:A").Select

With Selection.Font

.Name = "Calibri"

.FontStyle = "Regular"

.Size = 11

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

 

With Selection

.HorizontalAlignment = xlLeft

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

End With

 

With Selection

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

End With

 

 

Range("A2:B2").Select

With Selection.Font

.Name = "Calibri"

.FontStyle = "Regular"

.Size = 12

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

 

' resize Macro

'

'

Rows("1:100").Select

Selection.RowHeight = 15

 

Range("A3").Select

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Range("B4:B99").Select

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

 

' autofit

 

Range("A3:B100").Select

Selection.Columns.AutoFit

 

Rows("1:1").Select

Selection.RowHeight = 40

 

Rows("2:2").Select

Selection.RowHeight = 20

 

Rows("3:3").Select

Selection.RowHeight = 18

 

Rows("4:99").Select

Selection.RowHeight = 15

 

Columns("C:F").Select

Selection.Columns.AutoFit

 

Columns("C:C").Select

Selection.ColumnWidth = 8

 

ActiveWindow.Zoom = 100

 

End Sub

Share this post


Link to post
Share on other sites
TimG

I was thinking this morning whether the encoding format may have changed, and if so, what effect could it have?

 

What is the best format to use? Unicode, UTF8, UTF7, UTF16, UTF32, ASCII, or ANSI?

I'm assuming ASCII?

 

Something may have been saved in UTF8, or maybe cutting and pasting from the internet has included some hidden characters perhaps?

 

How can I check, and correct any issues relating to this?

 

What is best practice?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×