Creating a superscript formatted cell in another location

**Jonhinch** · 11-07-2016, 02:23 AM

Hello,

I am a maths teacher and am trying to develop some spreadsheets that automatically generate questions with Powers/Indicies in them. I want to create a function that inputs the cell name I want to write to and can then format the output with superscript as I wish. I have seen code that uses Worksheet_Change to format some cells but my VBA is still very limited and I can't think how to adapt it to write in a different cell and based on a function. Is someone able to give me a mechanism for doing this please? My complete function might be GenerateQuadratic(a4, DifficultyLevel) so in A4 there would then appear x²+2x+1=3. Thanks

**6StringJazzer** · 11-07-2016, 12:33 PM

It sounds like what you are thinking you want in your head is bigger than what you have written out here. That is, your title talks about superscripts but your description sounds like you want to generate complete expressions from some set of parameters.

As a first step, this is how code change an individual character to a superscript:

Please Login or Register  to view this content.

where R is a Range. This will superscript the 4th character in that cell.

GenerateQuadratic(a4, DifficultyLevel) so in A4 there would then appear x2+2x+1=3

But this opens a lot of questions. This looks like you want to generate an expression from scratch, not apply superscripts to an existing expression. How are you using DifficultyLevel? How did you determine that the coefficient of x should be 2? That the constant term should be 1?

**Jonhinch** · 11-07-2016, 02:10 PM

Hi, thanks for the reply. I can cope with generating the string but basically I want to take "x2+4x=1=3" put it into A4 and then superscript the 2. The difficulty I have is knowing how to make VBA write to a specific cell and then how to make it change the specific characters (which I will know from generating the string) into superscript.

Thanks if you can help. I have a feeling this is pushing excel a bit but hopefully I am wrong.

**6StringJazzer** · 11-07-2016, 04:00 PM

Excel may not be the very best to do this, but it can certainly be done.

Here is how you would take that equation, put it into A4, then superscript the 2. This assumes that the exponent will always be the second character in the string and is a single digit (a safe bet for quadratic equations).

The first line puts the equation as a string into cell A4. If this code is in a worksheet module, it will be A4 of that worksheet. If it is in a generic module (like Module1) then it will be whatever sheet is active. You can use a string variable instead of putting the actual equation in the code. You could also put a function call there that returns a string.

The second line does the superscript formatting of the second character.

Please Login or Register  to view this content.

**Jonhinch** · 11-07-2016, 04:36 PM

Oh as simple as that! Great.

Hmmm sorry. I have just tried it.

I have created "module 1" and put the following code in it.

I get "#Name" with the 'Range("A3").Value = "x2+4x+1=3"' added. It is fine without. I managed a test in another piece of code and that was fine so I don't know why it is failing in my striped down bit.

Sorry I can't put my code in correctly.

Please Login or Register  to view this content.

**6StringJazzer** · 11-07-2016, 05:02 PM

I don't understand how you are trying to use a Function here. If you are using it in a worksheet, you cannot assign values to cells this way. You can only return a value that will appear in the cell where the function is called. You cannot format individual characters in a result from a worksheet function.

If your idea was to write a user-defined function to return a quadratic with subscripts to a cell, it won't work. You have to set something up for a macro to populate the cell by pushing a button or something.

I have attached an example where I implemented my solution in a worksheet. The Sub is in the worksheet module.

Application.Volatile is an option to use in a function that must be recalculated any time there is any change whatsoever in the workbook. Why did you include it?

**Jonhinch** · 11-07-2016, 05:18 PM

Thanks again. The issue is I want the functions to generate code so that I can hit F9 and get a brand new set of questions instantly. If i have a subroutine how to get trigger it? Will I be able to trigger it and get say 30 calls to the subroutine and therefore 30 new questions? A function that writes to a different specified cell is the only way I can think of making this work. As I say perhaps I am trying to push excel too far. Maybe I should try and work in word but I have produced a nice sheet with lots of progressing questions on it in excel and I was hoping to extend it further into quadratics. I have attached the files so you can see what I have done and what I am trying to achieve.

**6StringJazzer** · 11-08-2016, 12:12 PM

Formatting the exponents as superscripts is a relatively minor issue compared to all the other work you have done to set this up in Excel. I would stay on track at this point. Doing it in Word would not be any easier.

I can get an idea of what you are doing there. If you create a sheet called Quadratic Equations for building the quadratics that works like the other sheets, then you would need yet another sheet for presenting the quadratics. That sheet would have a macro that would copy the results from the Quadratic Equations sheet, turn them into string values, and set the superscripts. (This could also be done for higher order polynomials, if you are thinking of going there.) It would be easier for me to help with that if you actually implement the Quadratic Equations sheet.

I also reviewed some of your code and have a few suggestions. For example, in SimEq:

The code should validate that lowerbound is lower than upperbound.
The code declares several variables as type Double but all these variables are assigned only Integer values. They could be declared as Integer or Long.
There are multiple If statements that test mutually exclusive conditions. This should be done as If/ElseIf, or better yet a Select Case. For example, change this:

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

**Jonhinch** · 11-08-2016, 02:34 PM

He he. I am an old Cobol programmer so these newer statements are still a bit hard for me to remember. I did well to discover the Do While :-) I did read a book at the weekend that listed the above so I will consider that for the future thanks.

I will look at producing my sheet and then repost here. Thanks for the offer off help.

**6StringJazzer** · 11-08-2016, 04:16 PM

I find this kind of question interesting so I will be happy to help

I never learned COBOL but I am an old Fortran, PL/1, JOVIAL, Ada, C, C++, and VBA programmer

**Jonhinch** · 11-09-2016, 02:20 PM

Hi, the code is rubbish and I was going to move it onto it's own spreadsheet but it just came up with #name and I didn't have time to find out why. It worked it this spreadsheet so here it stays for now.

I didn't have time to make my if's nice either sorry. It is all irrelevant unless there is a way to now take my "output" and format it as I want in say column B.

Thanks again

**6StringJazzer** · 11-10-2016, 12:58 PM

I found a problem in implementing this. You are using Application.Volatile, which recomputes the function any time there is any change anywhere in the workbook. I developed code to take the equations and reproduce them on a different sheet using the exponents. But it won't work because every time the code copies an equation to the display sheet, it forces all the equations to be updated again.

I have revised your file to use a button to refresh the equations, and using a macro solution instead of a worksheet function solution. Hopefully this will meet your needs. To do this I used columns L, M, N to put the parameters for the calls to QuadEquation. The macro then generates the equation with solution in column O. I think it will be clearer when you look at it.

I also recommend you use Option Explicit to guarantee that all variables are declared.

**6StringJazzer** · 11-10-2016, 01:02 PM

By the way if you like this I can help do the same thing on your other two sheets, although they don't need exponent formatting.

**Jonhinch** · 11-10-2016, 02:50 PM

That is fantastic. Well done. I am really grateful. I thought I might have to give up completely on the concept. Now I will be able to play and refine it all.

It did occur to me on my run this morning that I forgot to make it so that I had a character before each superscript letter. I think that will make it easier to know when a letter is superscript and when not. IE if I want questions such as 3^22X3^21=3^43 not that I would have the numbers that big unless I really wanted to stretch my classes. :-)

I burnt out last weekend playing with this and got in trouble for being a workaholic so I might have to play and improve this over time. Trouble is that programming bug is still in the system and now I am dabbling I can think of ways I can keep improving my lessons :-)

Do Until Mid(Eq, Ch, 1) Like "[a-z]" Or Ch > Len(Eq) is looking more like C++ than Cobol so I will have to find out what that is all about. I am guessing you are looking until you get the first letter of the alphabet then you are changing the next character after that.

Therefore I expect it will be easier to have

Do Until Mid(Eq, Ch, 1) ="^" Or Ch > Len(Eq)

And then I could have as many superscript characters as I would want.

I assume putting my "Answers" box on the page will still work and that it will hide column b once I change it.

Oh I am really pleased.

Thanks again

**6StringJazzer** · 11-10-2016, 04:32 PM

Originally Posted by Jonhinch

[...]I forgot to make it so that I had a character before each superscript letter.

That would make things easier. For the quadratics I assumed mathematical convention that the first term is the second-order term, and the variables are lower-case letters, so I just look for the first lower-case letter and superscript the next character. I also assume that the exponent will be only 1 digit.

Do Until Mid(Eq, Ch, 1) Like "[a-z]" Or Ch > Len(Eq)

Mid is a substring function. Mid(Eq, Ch, 1) returns the substring of Eq starting at character index Ch with a length of 1 character.

VBA has a pattern matching operator called Like. It's a little crude but I haven't seen this built into any other language besides SNOBOL. [a-z] is the pattern and so Like will return True if the character matches any letter in the range a-z.

Len returns the length of the string. So this looks for the first character that is a lower-case letter, or until it reaches the end of the string.

And then I could have as many superscript characters as I would want.

Correct!

I assume putting my "Answers" box on the page will still work and that it will hide column b once I change it.

Yes, it should be able to work that way.

**Jonhinch** · 11-13-2016, 05:28 PM

OK I have had a play today and I have managed to get a method formatting where I have "^" characters. I am using an array to store the position of which characters need the superscript.

I will need the regenerate questions call to check the answer for the character two and ultimately several more pairs of columns (Bronze, Silver, Gold levels). I am assuming Cells(R, "A") = Eq could also be written Cells(R, 1) = Eq and therefore I can have a final processing section that loops through Cells(R, C) where C is the columns in use.

Was there some sort of command that would output to a cell minus "^" characters? I am not convinced it would help me if there was. I think I need the array and I think the array will now need 3 levels.

I know there is no error checking going on but I am not too worried about that for my own use. Also i have tried the ElseIf command.

I am wondering if it would be best to start my working columns in say "AA" (so that I know it is for Column A) but since I think I need to work in numbers I don't see that would help much.

I feel I am getting close to the way it all needs to be. What a shame I have run out of time again.

**6StringJazzer** · 11-14-2016, 11:29 AM

I am not completely following your strategy but if it works, great. I am unclear why the 30 x 30 array is coming into play.

In any case, what further help do you need? I am not seeing a clear question in your latest post, other than "Was there some sort of command that would output to a cell minus "^" characters?" The Replace function will do that but you are already outputting the string minus the "^" characters, so not sure why you need it.

**Jonhinch** · 11-14-2016, 03:43 PM

Basically I am reading what is in the cell with the extra "^" characters in. I am logging where the characters that will need Superscripting are in the array and deleteing the "^" characters as I use your output mechanism to write it to the correct cell. I am then going through a Superscripting phase based on the infomation I have stored. I can't recall why I split the last two processes now. Perhaps I don't need too. I think I started it because the "EQ" string is compiled and written out at the end of a process and I needed all of the Superscript points stored for that. I don't think I need to know them for all cells at once. I perhaps went wrong. I think broke the process because I need to check the question column and the answer column for superscripts. I will investigate and play more.

What I think would be helpful is if I could write out questions and answers with the "^" character in using "function" called your way. Then if I could read the output and convert it to superscript as per the "^" characters. Is it possible to give me a piece of code that writes it out once (I have that) then reads it in again so I can then convert it. So I call "QuadEquation" that writes to (a,3) and (b,3) for the answer (a,4)(b,4) etc. I then call a procedure where my code converts all the the cells in range say "A3:B18" to Superscript.

I am convinced this will confuse you still but lets try.

**Jonhinch** · 11-15-2016, 02:10 AM

Do you know, I think you have given me everything I need and I just need to practice my coding more. Because your helpful, I have asked a question I don't need to. I will play and see if I can resolve the rest without further questions. Thanks for your help. I will see if my next post can be when I am pleased because I have done it :-)

**Jonhinch** · 11-15-2016, 03:31 PM

I had a free lesson at work and i was able to do the sheet just as I want. Once again thank you very much for you help. It is much appreciated.

Creating a superscript formatted cell in another location

LinkBack

Thread Tools

Rate This Thread

Display

Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Re: Creating a superscript formatted cell in another location

Thread Information

Users Browsing this Thread

Similar Threads

how to group conditionally formatted cells in a new location and retain column info

Creating a list from particular conditional formatted cells

Creating variables based on cell location

creating a formatted sheet with formulas and functions

Creating formatted Templates

[SOLVED] Dynamically creating formatted tables from raw data

Converting 'General' formatted cells to Text formatted cell using.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions