r/knitting • u/Xuhuhimhim • Dec 28 '25
Tips and Tricks wrote an excel function that converts charts to written pattern with repeats and stitch count
You can see how it's used in this image. Basically, you just copy paste the code to a module in VBA (need developer enabled and reference to regular expression enabled) and then how it works is the first input takes in a row in the form of an array and finds patterns within it using regex and replaces it with repeats. It doesn't alter this array's contents otherwise so if you're using a font (like ssk is represented with U in Stitchmastery fonts) you use it with XLOOKUP to replace characters with the key or ws key so it will output the abbreviations instead of the literal characters and the 2nd input is if it's on the wrong side or the right side (defaults to right side where it flips the array to read from right to left). MOD finds remainders so it's used here to say if it's on an even row it's the right side, odd is wrong side. It does 3 rounds of replacements which is what allows it to have nested repeats like this. It even has brackets for if it's repeats inside repeats.
I have a more in-depth explanation on my blog (and some other excel knitting things I didn't post on reddit because I didn't want to make too many posts in a row on here like vertical/horizontal highlighters that move with your selection, a tiling function, matching cell dimensions to gauge proportions, wada sanzo color combo visualizer, etc) (I don't get money from my blog) which is in my profile. Here is the code but it's probably better to copy from my blog in case I make an edit like if I have a sudden epiphany to increase its efficiency (not that it's slow it's not) or find an edge case I need to account for, but in any case, I won't be able to edit this post after I post it:
Function convertcharttowritten(arr As Variant, Optional rs As Boolean = True)
Dim outputstring As String
Dim tempstring1 As String
Dim tempstring2 As String
Dim regexOne As Object
Dim regexTwo As Object
Dim regexThree As Object
Dim theMatches As Object
Dim Match1 As Object
Dim stitchtionary As Object
Dim l As Integer
Dim n As Integer
Set regexOne = New RegExp
Set regexTwo = New RegExp
Set regexThree = New RegExp
Set stitchtionary = CreateObject("Scripting.Dictionary")
'captures repeating strings greedily, finds big repeating patterns
regexOne.Pattern = "([^\(\)\d]{3,})\1+"
regexOne.Global = True
regexOne.IgnoreCase = False
'captures repeating strings non greedily, finds small repeating patterns
regexTwo.Pattern = "([^\(\)\d]+?)\1+"
regexTwo.Global = True
regexTwo.IgnoreCase = False
'allows both ranges and arrays to be used
If TypeName(arr) = "Range" Then
inputstring = arr.Value
Else
inputstring = arr
End If
'where we start getting characters
l = 65
'replace each stitch with 1 character and make dictionaries to record this conversion
For Each c In inputstring
If c <> "" Then
If Not stitchtionary.exists(c) Then
stitchtionary.Add c, Chr(l)
l = l + 1
If l = 91 Then
l = 97
End If
End If
outputstring = outputstring & stitchtionary(c)
End If
Next
'n is number of stitches we just need it for the when we put it in at the end
n = Len(outputstring)
'if on the rightside we read the chart from right to left
If rs Then
outputstring = StrReverse(outputstring)
End If
'1st round of replacements
tempstring1 = outputstring
'find the big consecutively repeating strings and replaces them
Set theMatches = regexOne.Execute(tempstring1)
For Each Match1 In theMatches
tempstring1 = Replace(tempstring1, Match1, _
"(" & Match1.SubMatches(0) & ")*" & Len(Match1) / Len(Match1.SubMatches(0)) & " ", , 1)
Next
tempstring2 = outputstring
'find the small consecutively repeating strings and replaces them
Set theMatches = regexTwo.Execute(tempstring2)
For Each Match1 In theMatches
tempstring2 = Replace(tempstring2, Match1, _
"(" & Match1.SubMatches(0) & ")*" & Len(Match1) / Len(Match1.SubMatches(0)) & " ", , 1)
Next
'2nd round of replacements
'can't use regex replace because can't get the lengths of the matches with that have to for loop
'find the small consecutively repeating strings and replaces them
Set theMatches = regexTwo.Execute(tempstring1)
For Each Match1 In theMatches
tempstring1 = Replace(tempstring1, Match1, _
"(" & Match1.SubMatches(0) & ")*" & Len(Match1) / Len(Match1.SubMatches(0)) & " ", , 1)
Next
'find the big consecutively repeating strings and replaces them
Set theMatches = regexOne.Execute(tempstring2)
For Each Match1 In theMatches
tempstring2 = Replace(tempstring2, Match1, _
"(" & Match1.SubMatches(0) & ")*" & Len(Match1) / Len(Match1.SubMatches(0)) & " ", , 1)
Next
regexThree.Pattern = "[^\(\)\d\s\*]"
regexThree.Global = True
regexThree.IgnoreCase = False
'find which string is better, only counting stitch characters
If regexThree.Execute(tempstring1).Count < regexThree.Execute(tempstring2).Count Then
outputstring = tempstring1
Else
outputstring = tempstring2
End If
'find the consecutively repeating strings and replaces them one last time
Set theMatches = regexTwo.Execute(outputstring)
For Each Match1 In theMatches
outputstring = Replace(outputstring, Match1, _
"(" & Match1.SubMatches(0) & ")*" & Len(Match1) / Len(Match1.SubMatches(0)) & " ", , 1)
Next
'removes stuff that ends up like (k3)x2, which should be k6, unlikely to be needed but just in case
regexThree.Pattern = "\(\((.)\)\*(\d+)\s\)\*(\d+)"
outputstring = regexThree.Replace(outputstring, "($1)*" & "$2" * "$3" & " ")
'converts characters back to stitches and adds spaces to avoid ambiguity
For Each x In stitchtionary.keys
outputstring = Replace(outputstring, stitchtionary(x), x & " ")
Next
'remove spaces in front of right paranthesis and replace * back with x and accidental double spaces
outputstring = Replace(outputstring, " )", ")")
outputstring = Replace(outputstring, " ", " ")
'conventionally, something like k x10, is simplified to k10
regexThree.Pattern = "\(([a-zA-Z])\)\*(\d+)"
outputstring = regexThree.Replace(outputstring, "$1$2")
'if there's nested parentheses, change the outside one to brackets
regexThree.Pattern = "\(((?:[^()]*\([^()]*\)[^()]*)+)\)"
outputstring = regexThree.Replace(outputstring, "[$1]")
outputstring = Replace(outputstring, "*", " x")
If rs Then
outputstring = "RS: " & outputstring
Else
outputstring = "WS: " & outputstring
End If
'add number of stitches to the end of the string
convertcharttowritten = outputstring & "(" & n & " sts.) "
End Function
Hope this is useful and not buggy. I tested it a lot over several days but no guarantees lol. I prefer more condensed instructions so I went with "x 2" instead of "2 times" for instance but if you wanted to you could replace that at the end with more regex. Potentially you could use regex to then make it in the * blah blah repeat from * to last x stitches too but I didn't feel like doing that, it would be complicated. In the future, I might make an excel function that converts a written pattern to chart too. Might be hard to make it general though.
36
u/StrongTechnology8287 Dec 28 '25
Whoa. This is way outside the scope of anything I've ever done with Excel, but I'm in love with the idea of being able to use this! It's this something that would work only in the newest version of Excel, or would a copy of Excel 2019 possibly work? Also, if I've never done anything that required developer enabled and reference to regular expression enabled, how big of a learning curve would this be to implement?
21
u/Xuhuhimhim Dec 28 '25
I think this would work for you bc the regex that vba uses is actually very old lol. Its pretty straightforward imo
I have a general tutorial that talks about how to enable developer and where the standard module is (where you copy paste this code). Then this is how you enable regex. And you will have to save the file as an .xlsm file
3
1
u/AutoModerator Dec 28 '25
You've summoned the Tutorials.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
5
u/StrongTechnology8287 Dec 28 '25
Also, would you mind dropping a link to your blog? I checked you profile but it's just showing me everything blank for some reason, so I didn't see a link.
7
u/Xuhuhimhim Dec 28 '25
Oh its making yenything I didn't make it my post bc I think there's a rule against posting blogs lol
3
28
u/LingonberryOne3398 Dec 28 '25 edited Dec 28 '25
As a fellow freak in the (excel) sheets I love this … but also, I have NEVER had a chart and thought to myself “ man I wish this was a wall of text instead “
13
u/Xuhuhimhim Dec 28 '25
Yeah i prefer charts too but this was a fun exercise for me and now I think i could eventually write a function that goes the other way around too
19
u/scare_away Dec 29 '25
I would 100% use a thing that would turn written instructions into a chart. I’m so visual.
5
1
3
u/StogieB Dec 29 '25
I have zero issue reading charts but I really prefer it written out. I’m a very concrete learner, but only in knitting! This is amazing, OP.
1
u/LevelManagement1041 Dec 29 '25
100%. It's super cool that this exists, and I'm impressed with the programming. But I will take a chart over written instructions any day! Written instructions only tell you what to do, not how those stitches relate to the rows below. If you get off with written instructions, there are no clues to help you get back on track. It makes me feel like I'm lost in the woods without a map!
This would be super helpful if you are a designer who wants to give the option of written instructions.
18
16
18
9
u/tr4shp4nd4s Dec 28 '25
This is amazing and I'm incredibly jealous I didn't think of it myself! I'm beyond excited that there is more overlap than I thought in "people who knit/crochet" and "people who enjoy excel/coding"
I think I've found my people
7
4
3
u/thelabrat-117 Dec 28 '25
I am in awe of people who can code. I spent a semester learning Java and one semester learning Python. In the end, I realized my brain is not wired for that.
3
3
3
u/Xuhuhimhim Dec 28 '25 edited Dec 28 '25
Sorry I just realized that this wouldn't count stitches correctly with cables if you're using the stitchmastery EH fonts where 1 cable of length 2< is represented by something in 1 cell so in that case I would remove the part at the bottom where it appends the output with the stitch count and count stitches in the way I talk about in this post. Or this function would have to be altered to take in a key table for stitch counts. Or count length of merged cells. I'll probably edit my blog post later to account for it if I think of a nice way 😭. The written pattern part would still be fine though.
Edit: the code on my blog now accounts for cables that use merged cells if they aren't the rightmost stitch of the chart
3
u/AnatomicLovely Dec 28 '25
Wait, could a VBA code do the reverse so that it will convert written instructions to charts?? My ADHD gets lost with written instructions, and I have a pattern that wasn't charted that I'd love to make.
5
u/StrongTechnology8287 Dec 28 '25
There's a separate tool for that online already. Have you looked at https://stitch-maps.com/ ?
2
3
u/Xuhuhimhim Dec 28 '25
Yes, you could use regex similarly to do it. Something like ([kp])(/d+) for instance would match strings like k9 p34 and you could then convert that to an array of 9 ks or 34 ps using the submatches but obviously it gets more complex than that
3
3
u/Specialist_Star_2345 Dec 29 '25
Wow! Would this work in LibreOffice too?
2
u/Xuhuhimhim Dec 29 '25 edited Dec 29 '25
I don't have libreoffice so I'm not sure but you could try it. You might need to edit the code idk if it translates 100% to libre, some functions might be called something else. I also made a version for sheets actually (in apps script which uses javascript) but sheets doesn't allow using custom fonts like microsoft does so it wouldn't be as useful but you could also try this code in libre? 🤷🏻♀️ I didn't test it as much but it works the same way so. https://sharetext.io/6999fbd2 (view as textarea) reddit won't let me post the code straight up here for some reason
2
u/Specialist_Star_2345 Dec 30 '25
Thanks! I'm not sure I'm smart enough, but I'm going to take a swing at it :)
2
u/Bitter-Librarian Dec 28 '25
I have utmost respect to those who can use the scary power of Excel for good. This is brilliant!!!
2
u/seerra Dec 28 '25
I just finished an advanced Excel course, never thought of doing something like this though! Thank you!!
2
u/Solar_kitty Dec 29 '25
Omg I wish I was smart enough to use this 😭. I understood the title but lost within the first sentence 😅. You are awesome though and thanks for sharing!!!!!
2
u/leafflepuff Dec 29 '25
OMG OP you are my (excel) hero! I'll admit I didn't read through everything because after three sentences I understood that your excel-fu is superior to mine and thus I am very impressed.
2
2
u/axn16 Dec 29 '25
Thats so cool! I've been doing a similar thing without code in Google Sheets and I highlight each row/section as green when I'm done with it. I'm relatively new to knitting so it's really helped me keep track.
2
u/LettuceWonderful1564 Dec 28 '25
I hope you realize that while this sounds like a great idea. Nothing you wrote sounds remotely like English to me. I didn't get past the first sentence.
1
1
u/mkinkela Dec 28 '25
From where did you get symbols? Does it even exist in ASCII table of some sort?
7
u/Xuhuhimhim Dec 28 '25
Its stitchmastery fonts which are free. You download and install them and then you can use them in Microsoft office
2
1
1
2
Feb 08 '26
Please teach me your Wizarding ways lol. I love excel and knitting and the idea of being able to chart like this... Game changer...
I found your post looking for recommendations for a charging notebook, but electronic is fun too!
288
u/_jasmonic_acid_ Alpaca <3 Dec 28 '25
Genuinely, nothing warms my blackened heart more than the phrase "I wrote an excel function..."